Agent Jobs

Old Trick To Change All Database Owners

The following sql logic will simply change all database owners to ‘sa’. It’s a tried and true method. Will affect all databases excluding the system databases.  Additionally; the final script will confirm all database owners have been changed to ‘sa’.  Anyone can do this, but thought I would post anyway to save you a few keystrokes.

 declare @change_db_owners	varchar(max) set @change_db_owners	= ''
select	@change_db_owners	= @change_db_owners +
'exec [' + name + ']..sp_changedbowner ''sa'';' + char(10)
from	sys.databases where database_id > 4
exec	(@change_db_owners)	--for xml path(''), type

select
'database' = upper(name)
,	'owner' = suser_sname(owner_sid)
from
sys.databases
where
database_id > 4
order by
name asc
Advertisements

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