What does it really imply that the database doesn’t have a database proprietor?! It appears odd, however, it’s potential. In some instances that the database is created by an utility it actually occurs that the database doesn’t have any database proprietor. As an illustration, the databases generated by CRM Deployment Supervisor doesn’t have database proprietor. So what if we had a bunch of databases within the SQL Server that doesn’t have any database homeowners? Okay, let me clarify. I’ve confronted to a state of affairs that we wanted to create some database diagrams for a lot of databases and all of these databases the place CRM databases created by CRM Deployment Supervisor. Therefore, after we had been attempting to generate a database diagram an error message had been elevating saying the database doesn’t have DB proprietor. So it was very time consuming if we needed to outline a DB proprietor for every database individually utilizing the GUI. The next code will outline a specific database proprietor for the databases server broad. (In our state of affairs it was a service consumer.)
declare @dbname nvarchar(max)
declare @dbs desk (no int, db nvarchar(max))
declare @counter int
declare @sql nvarchar(max)
declare @consumer nvarchar(max)
set @consumer = ‘DOMAIN_NAMEDB_OWNER’
insert into @dbs (no, db)
choose row_number () over (order by title) Quantity, title
from sys.databases
the place owner_sid != 0x01
set @counter= (choose max(no) from @dbs)
choose * from @dbs
whereas @counter>=1
start
set @dbname= (choose db from @dbs the place no=@counter)
set @sql = ‘ALTER AUTHORIZATION ON DATABASE::[‘+@dbname+‘] TO [‘+@user+‘]’
exec sp_executesql @sql
set @counter=@counter–1
finish;
You’ll be able to power the code to only outline the DB proprietor for some databases. In our case all of the databases had a “MSCRM” suffix so I simply wanted so as to add one other standards to the “the place” clause as under:
choose row_number () over (order by title) Quantity, title
from sys.databases
the place owner_sid != 0x01 and title like ‘%MSCRM’
Simply DO NOT neglect to vary ‘DOMAIN_NAMEDB_OWNER’ to no matter you need after which press F5.
Executed! Simple!
Associated
Supply hyperlink
