How to define database owner SQL server wide

What does it actually mean that the database doesn’t have a database owner?! It seems odd, but, it is possible. In some cases that the database is created by an application it really happens that the database doesn’t have any database owner. For instance, the databases  generated by CRM Deployment Manager doesn’t have database owner. So what if we had a bunch of databases in the SQL Server that doesn’t have any database owners? Okay, let me explain. I’ve faced to a situation that we needed to create some database diagrams for many databases and all of those databases where CRM databases created by CRM Deployment Manager. Hence, when we were trying to generate a database diagram an error message were raising saying the database doesn’t have DB owner.  So it was very time consuming if we wanted to define a DB owner for each database individually using the GUI. The following code will define a particular database owner for the databases server wide. (In our situation it was a service user.)

declare @dbname nvarchar(max)

declare @dbs table (no int, db nvarchar(max))

declare @counter int

declare @sql nvarchar(max)

declare @user nvarchar(max)

set @user = ‘DOMAIN_NAME\DB_OWNER’

insert  into @dbs (no, db)

select  row_number () over  (order by name) Number, name

from sys.databases

where owner_sid != 0x01

set @counter= (select max(no) from @dbs)

select * from @dbs

while @counter>=1

begin

set @dbname= (select db from @dbs where no=@counter)

set @sql = ‘ALTER AUTHORIZATION ON DATABASE::[‘+@dbname+‘] TO [‘+@user+‘]’

exec sp_executesql @sql

set @counter=@counter1

end;

 

You can force the code to just define the DB owner for some databases. In our case all the databases had a “MSCRM” suffix so I just needed to add another criteria to the “where” clause as below:

select  row_number () over  (order by name) Number, name

from sys.databases

where owner_sid != 0x01 and name like ‘%MSCRM’

 

Just DO NOT forget to change ‘DOMAIN_NAME\DB_OWNER’ to whatever you want and then press F5.

Done! Easy!

Leave a Reply