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=@counter–1
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!
Discover more from BI Insight
Subscribe to get the latest posts sent to your email.
