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