How to change the database owner for the secondary replica database

When you perform a restore of a database to a SQL Server Instance where the database doesn’t exist, the owner of the database is changed to the user that is executing the restore. Actually, this also the case for attaching a database. The ownership can give the login extra permissions which in some cases you don’t want. A good rule of thumb is to choose “sa” as the database owner. This can be easily done by executing the following query after the restore is done.

ALTER AUTHORIZATION ON DATABASE::<YourDatabaseName> to sa;

Just keep in mind, when you change the owner of the database that the user that owned the database might have less or no permission anymore on the database.

Unfortunately, if you restore a database as read-only or if you leave it into restoring mode for AlwaysOn Availability Group configuration, you cannot change database owner anymore.
Even if you setup an AlwaysOn Avaliability Group with the wizard of the SQL Server management studio, the owner of the database on the secondary replica will be the user that is executing the wizard. Quite obvious because in the background the wizard is performing the restore J.
Your database must be in a read/write mode before you can change the database owner.

If the database is read-only, it can briefly be set to Read/Write for the modification, and then set back. That shouldn’t be an issue.
In case of AlwaysOn Availability Groups, you will have to perform a failover to the secondary replica, change the database owner and failback to the original primary replica.
But, I can imagine that there will be situations where a failover will not be possible, like for example asynchronous commit mode or just the business that doesn’t want downtime.

A possible workaround could be to manually prepare the secondary database for the availability group but perform the restore as the user sa. The following script can be used

EXECUTE AS login='sa'
RESTORE DATABASE [YourDatabaseName] FROM DISK = '<YourBackupPath>' 
REVERT
GO

More information on Manually Prepare a Secondary Database for an Availability Group can be found here

It’s a simple script but it does change the owner of the database to sa and you don’t have to worry about a failover to set the correct database owner.

Pieter

Leave a Reply

Your email address will not be published. Required fields are marked *