Change database owner for multiple DBs in one Go

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Note:  Best viewed in Internet Explorer 6 and above

Implementation: How to Change database owner for multiple DBs in one Go

Scenario: Lets say, you want to change DB owner for multiple DBs to say ‘SA’ in case  when the old owner is not active user/account on SQL server.

Infrastructure: The below query/procedure has been run/tested successfully on SQL server 2005,2008,2008 R2 and 2012 resp.

Resolution:

select ‘use ‘ + ‘[‘+ name + +’]’+  ‘;’ +’ ‘+ ‘exec sp_changedbowner ‘+’ ‘+ ”’sa”’ + ‘;’  from sys.databases

where suser_sname(owner_sid)  in (‘ ‘)

You can put the filter clause as per the requirement.

Hope this helps!