T-SQL Script Using sp_msforeachdb to Change All Databases’ Owner to ‘sa’ Account

Oftentimes, some DB’s in our inventory are modified or restored or created by different user accounts, which leads to having various DB owners in an instance. And a good ‘best practice’ claimed by Microsoft is to align all DB owners to the ubiquitous ‘sa’ account and here goes the script to do so without offending all the system DB’s whose system catalogs can’t be modified since version 2005.


/*
Script to change the owners of all DB's to 'sa'

Applied version: 2000, 2005+
*/


EXEC sp_MSforeachdb
@command1='use [?]; if db_name() not in (''master'', ''model'', ''tempdb'', ''msdb'', ''distribution'') exec sp_changedbowner ''sa'''
go

Caution on applying this Microsoft labeled ‘best practice’, see the excerpt from ‘Microsoft EDW Architecture, Guidance and Deployment Best Practices’:

Database ownership, in fact, refers to the SQL Server login that owns the database, as far as the master database is concerned. Because that login is the owner, it inherits db_owner rights in the database, but database ownership by itself is not a permissions property.

Please note that the login ‘inherits db_owner rights in the database’, so if you change this DB’s owner to ‘sa’ account, the old login (be it AD or SQL) will automatically lose the db_owner privilege on this DB and this kind of behavior might not be intended.

Therefore, a much much safer way to update all DB’s owners to ‘sa’ account would be to make sure beforehand that the old owner account be assigned ‘db_owner’ role.

 

Advertisements

About Nick Xu

Not a DBA anymore
This entry was posted in T-SQL Script and tagged , . Bookmark the permalink.

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