Featured

Change Database Owner Across All Databases Using Alter Authorization

You noticed that all the database owners are set to the wrong account or person, and you need a simple way to check all databases for their database owners. Here’s alittle script you can run to get all database owners across all databases.

select
'owner' = suser_sname(owner_sid)
, 'database name' = name
from
sys.databases
where
name not in ('master', 'model', 'msdb', 'tempdb') order by database_id asc

So now you can see all the owners across the databases. Ok; so if you’re like most enterprises you’ll notice there’s some inconsistencies probably. Next you’re thinking; What’s the best, and most current way to change all the database owners in one go? Here’s where you can run the ‘alter authorization’ statement, but… you don’t want to do it for each database one at a time. Why not ALL databases? Excluding the system databases of course.

Here’s some logic to make that happen followed by the owner query above to verify. I threw in a 5 second delay between statements just for kicks.

use master;
set nocount on
declare @change_database_owner varchar(max)
set @change_database_owner = ''
select @change_database_owner = @change_database_owner +
'alter authorization on database::[' + replace(name, '''', '''') + '] to MyNewDBowner;' + char(10) from sys.databases where name not in ('master', 'model', 'msdb', 'tempdb') exec (@change_database_owner) --for xml path(''), type

waitfor delay '00:00:03'

select
'owner' = suser_sname(owner_sid)
, 'database name' = name
from
sys.databases
where
name not in ('master', 'model', 'msdb', 'tempdb') order by database_id asc
Advertisements

Categories: Featured

Tagged as: , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s