Thursday, February 24, 2011

A Quick Way to Change the Owner on All User Databases

We like to keep the SA account as the owner on all of our user databases, but when we started restoring backups to a new server we were testing, we noticed that the owner of the restored database was the DBA who did the restore. When a database is being created for the first time on a server, SQL sets the owner of the database as the person who restored the database. If someone changes the owner, and restores a backup over the existing database, the change will be retained.

So I needed a quick way to set the owner on all user databases and this is what I came up with:

exec sp_msforeachdb 'use ? if (select db_name()) not in (''master'',''msdb'',''model'',''tempdb'')
Begin
   Alter Authorization on Database::? to <login>
      End'

No comments:

Post a Comment