Generating script for mass change of database owner

This is a very simple approach to generate mass script for changing database owner of all user databases. Why would you need that? Well there are couple of reasons – you might have mass detached\attached all user databases (in an upgrade scenario for example) or you might have restored numerous databases on a brand new instance. Then for sure you will not have the ‘sa’ as owner of the database. In this case, use the below script to get the ownership fixed. Note that it only works for SQL Server 2005 and above!

select 'ALTER AUTHORIZATION ON DATABASE::[' + name + '] TO [sa]'  
from sys.databases where database_id>4

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.