SQL Server Availability Group – DB Owner Not Updating Automatically

availability-groupsSecuritysql server 2014

I learned a hard lesson this week. It turns out what while table updates/ schema updates do get replicated across databases on an Availability Group, changing the DB Owner on the primary replica does not change the DB owner on the secondary replica automatically.

Thus if you perform a failover, the database owner could now be out of sync with the master table's SID.

Should my plan of action when e.g. making a security related change be to

1. Apply change to primary replica
2. Fail over the Availability Group
3. Apply change to the new primary replica

Am I correct in making these statements? Is it only DBOwner/ Schema changes that I must be aware of when I am making changes.

Best Answer

Yes, you need to make sure that databases have the same owner, schema changes will travel along the availability group. This gets problematic when there are changes in database owner as you need to reapply those changes too to the secondary nodes.

If it is possible, you could change owner to SA on every database, then make this change every night and when fail-over happens. When you capture the fail-over you can run this to make SA owner for every database:

EXEC sp_MSforeachdb 'ALTER AUTHORIZATION ON DATABASE::? TO SA';

EDIT:

If you don't wish to go SA as a owner, you could create table containing the database owners

CREATE TABLE DatabaseInAvailabilityGroup.dbo.dbownersMaintenance (
id INT IDENTITY(1,1),
dbname NVARCHAR(100) NOT NULL,
username NVARCHAR(100) NOT NULL
);

Filling up the table (make this as a job and schedule it once a hour or something)

DELETE FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance;

INSERT INTO DatabaseInAvailabilityGroup.dbo.dbownersMaintenance
SELECT db.name, sl.name
FROM master.sys.databases db
INNER JOIN master.sys.syslogins sl ON db.owner_sid = sl.sid;

As soon as the fail-over has happened, this needs to be run on the new active node:

DECLARE @maxRow INT, @currentRow INT, @sql NVARCHAR(MAX), @dbname NVARCHAR(100), @owner NVARCHAR(85);
SELECT @maxRow = MAX(id), @currentRow = MIN(id) FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance;

WHILE @maxRow >= @currentRow
BEGIN
SELECT @dbname = dbname, @owner = username FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance WHERE id = @currentRow;
SET @sql = 'ALTER AUTHORIZATION ON DATABASE::'+@dbname+' TO ['+@owner+']';
EXECUTE (@sql);
SET @currentRow = @currentRow+1;
END
Related Question