/* The Code */
--If you want to get a list of current owners who aren't sa
SELECT suser_sname(owner_sid) as DBOwner,
Name as DBName
FROM sys.databases
WHERE suser_sname(owner_sid) <> 'sa'
--To selectively set database owner to SA
DECLARE @OwnerSQL as varchar(max) = ''
SELECT @OwnerSQL =
'ALTER AUTHORIZATION ON DATABASE::' + QUOTENAME(Name) + ' to sa;'
+ char(10) + @OwnerSQL
FROM sys.databases
WHERE suser_sname(owner_sid) <> 'sa'
AND [state] = 0
--other where clauses can be added here as needed to prevent changing specific databases
--print out the change script
Print @OwnerSQL
--execute the script
EXEC (@OwnerSQL)
--Finish up by get a list of owners now
SELECT suser_sname(owner_sid) as DBOwner,
Name as DBName
FROM sys.databases
/* End Code */
Comments