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.