Fix the database owner of all online SQL Server databases to ‘sa’

Below script will change the ownership of all the online SQL server databases whose current owner is not equal to ‘sa’. In many occasions the databases restore is performed by the DBA level user and owner remains unchanged.  The best practice is to change owner of the all SQL Server databases to ‘sa’ account.  You can use alter authorization starting from SQL Server 2012, if you are on a previous version of SQL Server you can continue using sp_changedbowner.

DECLARE @DBName SYSNAME

DECLARE @SQL NVARCHAR(300)

DECLARE cur_FixDBOwner CURSOR FOR

SELECT d.[name]

FROM sys.databases d

WHERE d.name not in ('master', 'model', 'tempdb')

AND d.owner_sid <> 0x01 AND d.state_desc='ONLINE'

OPEN cur_FixDBOwner

FETCH NEXT FROM cur_FixDBOwner INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @SQL='ALTER AUTHORIZATION ON DATABASE::'+QUOTENAME(@DBName)+' TO [sa];'

PRINT (@SQL)

EXEC  (@SQL)

FETCH NEXT FROM cur_FixDBOwner INTO @DBName

END

CLOSE cur_FixDBOwner

DEALLOCATE cur_FixDBOwner