I’ve spent some time in a couple AlwaysOn shops and if you don’t manage it well, you can quickly accumulate secondary databases that are owned by DBA accounts. Naturally, secondaries are read-only so you can’t simply change the owner to SA. One option is to fail over the AG and change the database owner but that would require downtime. A better option would be to prevent this situation all together.
To start, SSMS allows you to script out the addition of a database to an AlwaysOn group. If you do so, you’ll find that under the hood the following actions are performed:
- Add the database to the AG on primary
- Perform a full backup on the primary
- Restore the full backup to the secondary
- Perform a log backup on the primary
- Restore the log backup to the secondary
- Begin data movement
We’re going to employ the EXECUTE AS statement, which I haven’t ever used outside of a certification exam… until now. Taking the script that SSMS generates, wrap the first restore statement (the full backup restore) in the EXECUTE AS statement. Something to the tune of:
:Connect ServerTwo EXECUTE AS LOGIN='sa' RESTORE DATABASE [MyDB] FROM DISK = N'\\ServerOne\MyDB.bak', NORECOVERY, NOUNLOAD, STATS = 5 GO REVERT
This will work even if the SA login is disabled but it will require you to be a member of the sysadmin role. Once the database has been restored to the secondary, it’s owner will be established as SA. I like this solution because it doesn’t require me to run the entire process while logged in as some other login. I can use my usual login and still keep my name off of any audits.