主页 / dba / 问题 / 155287
Accepted
Peter PitLock
Asked: 2016-11-16 02:10:04 +0800 CST

SQL Server 可用性组不会自动更新数据库所有者

  • 772

这周我学到了一个惨痛的教训。事实证明，虽然表更新/模式更新确实在可用性组上的数据库之间复制，但更改主副本上的数据库所有者并不会自动更改辅助副本上的数据库所有者。

因此，如果您执行故障转移，数据库所有者现在可能与主表的 SID 不同步。

在进行安全相关更改时，我的行动计划是否应该是

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

我做出这些陈述是否正确？当我进行更改时，我必须注意的只是 DBOwner/Schema 更改吗？

security sql-server-2014

1 个回答

  1. Best Answer

    是的，您需要确保数据库具有相同的所有者，架构更改将沿着可用性组进行。当数据库所有者发生更改时，这会出现问题，因为您需要将这些更改也重新应用到辅助节点。

    如果可能，您可以在每个数据库上将所有者更改为 SA，然后在每晚以及发生故障转移时进行此更改。当您捕获故障转移时，您可以运行此命令以使每个数据库的 SA 所有者：

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

    编辑：

    如果您不想将 SA 作为所有者，您可以创建包含数据库所有者的表

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

    填满桌子（把它作为一项工作，每小时安排一次或其他什么）

    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;

    一旦发生故障转移，就需要在新的活动节点上运行：

    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
    • 2

相关问题