The best way to outline database proprietor SQL server broad

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


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=@counter1



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!

Supply hyperlink

Leave a Reply

Your email address will not be published. Required fields are marked *