T-SQL query to Change the Database Owner(DBOwner) to SA for all databases

How many times you have come across the situation where the Database Owner for user databases on your server is not consistent and different databases have different owner. This is not mandatory to have a single user as DBOwner for all databases though. There might be scenarios where a developer or tester or any other person has created database and he is the owner for it and he has left the organization now. Such as there can be various scenarios where the database is owned by different users.

Best way is to check periodically on all of your servers and change all Database owners to SA account. Below is the T-SQL script to change all the user databases which are in online state to SA account.

USE master
GO
--Variable Declaration
 DECLARE @myDBName NVARCHAR(Max)  
 DECLARE @ExecSQL NVARCHAR(Max) 
 
--Declare Cursor to fetch Database Names
 DECLARE myDBCur CURSOR FOR 
 SELECT name FROM master.dbo.sysdatabases  
 WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')--To Make Sure SystemDB are not included

 OPEN myDBCur 
 FETCH NEXT FROM myDBCur INTO @myDBName 
 WHILE @@FETCH_STATUS = 0 
 BEGIN 
  --Check if the Database is in Online State
  IF EXISTS(select top 1 [name] from sys.databases where [name] = @myDBName and [state] = 0)
   BEGIN
    set @ExecSQL = 'USE [' + @myDBName + ']; EXEC sp_changedbowner ''sa'''
    --Uncomment Below Statement to directly Execute
    --EXEC (@ExecSQL) 
    --Print the Command to use for future Use
    PRINT @ExecSQL
    FETCH NEXT FROM myDBCur INTO @myDBName
   END
 END 
 CLOSE myDBCur  
 DEALLOCATE myDBCur
GO