Availability Groups DB Owner Setting to SA (Setup Tips)

One of the most annoying things about AG groups is that when you use SSMS to add databases and replicas to AG groups the databases end up with the user running SSMS as the DBOwner.

To make matters worse often by the time this is realized the db is in production and read only meaning that it is not easy to change. Unless you failover the AG groups to do so.

If you are careful from the outset you can set up new databases and ensure that the DBOwner is set to sa.

Here are the simple steps to do so. We don’t go into great detail of how and why each step is done as it is assumed if you are doing this kind of thing on Availability Groups you have already grasped the fundamentals.

On the Primary Group

Add the database to the AG Group but choose the option to manually synch.

Make sure the Db owner is set to sa.

Make a full backup of the database in a share that is accessible by the other replicas.

On the Secondary Replica(s)

--Now connect to replica node and restore fully
USE [master] RESTORE DATABASE [] FROM DISK = N'\\DBBackups\.bak' WITH FILE = 1, NOUNLOAD, STATS = 5 GO 

--change the dbowner 
USE EXEC sp_changedbowner 'sa' 

--now restore again this time with no recovery 
USE [master] RESTORE DATABASE [] FROM DISK = N'\\DBBackups\.bak' 
WITH FILE = 1, REPLACE, NORECOVERY, NOUNLOAD, STATS = 5 GO 

-- Now restore log file but still leave in norecovery 

RESTORE LOG [] FROM DISK = N'\\DBBackups\.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10 
GO 

--join secondary to ha group ( you must have already added the db to the primary node having skipped the synchronization steps) 

ALTER DATABASE [] SET HADR AVAILABILITY GROUP = [] ; 

Remember to add any logins to each server as well. Check the synchronization reports to ensure everything is synched up and there are no warnings or errors.

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply