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