SQL Server 登录账户权限异步同步

在SQL Server 故障转移（高可用方案）中，除FCI（实例级故障转移）外，无论是Always On、Replication、Log Shipping 、Databaes Mirroring，还是异步备份还原，都容易忽略一个问题，那就是SQL Server 登陆账户的同步，因为数据库级别的高可用方案都没有同步登陆账户（除了SQL Server 2012 的Contained Database，这种数据库能将login user/pwd包含于db级别中）。尤其对于一个大型的线上业务环境，动辄几十上百个登陆账户的实际应用场景。

本文将使用《SQL Server 登陆账户权限克隆》中SQL Server 登陆账户权限克隆过程sp_DBA_LoginPermissionsClone，实现SQL Server 登陆账户权限的异步同步。这里的同步，只将生产服务器上SQL Server 登陆账户的创建脚本及权限的赋予脚本同步到备用服务器，并不实时创建修改（因为删除账户后再重新创建同名的账户需要重启服务）。

准备工作：

• 在备份机器SQL Server 的master 库上创建一个物理表dba_loginPermissionSQL，用于留存生产服务器上SQL Server登陆账户的创建脚本和权限赋予脚本

• 创建对表loginPermissionSQL的插入过程dba_loginPermissionSQL_Insert、删除的存储过程dba_loginPermissionSQL_Delete

• 在备份机器SQL Server 实例上创建登陆账户，并赋予执行存储过程dba_loginPermissionSQL_Insert、dba_loginPermissionSQL_Delete的权限

• 在生产机器上创建链接到备份机器的链接服务器（如果生产机器和备份机器的网络不通，还需要打通网络策略）

• 确定生产服务器上需要同步的SQL Server账户

在备用服务器上执行如下脚本（账户密码自行调整）：

--在备用服务器上执行
USE master
GO
CREATE TABLE dba_loginPermissionSQL(loginPermissionCreateSQL VARCHAR(max),createDate DATETIME)
CREATE LOGIN link_loginClone
WITH PASSWORD='passWord'
,CHECK_POLICY=ON
,CHECK_EXPIRATION=OFF
CREATE USER link_loginClone FROM LOGIN link_loginClone
GO
CREATE PROC dba_loginPermissionSQL_Insert
      @sql VARCHAR(max)
AS
BEGIN
      SET NOCOUNT ON
      INSERT INTO dba_loginPermissionSQL(loginPermissionCreateSQL ,createDate)
      VALUES(@sql,GETDATE())
      
END
GO
CREATE PROC dba_loginPermissionSQL_Delete
AS
BEGIN
      SET NOCOUNT ON
      DELETE dba_loginPermissionSQL
      WHERE createDate<CAST(GETDATE() AS DATE)
END
GO
GRANT EXEC ON OBJECT::dbo.dba_loginPermissionSQL_Insert TO link_loginClone
GRANT EXEC ON OBJECT::dbo.dba_loginPermissionSQL_Delete TO link_loginClone

在生产服务器上创建：

注意，这里需要替换创建链接服务器脚本中的备用服务器IP、端口、登陆名、登陆密码。

sp_DBA_LoginPermissionsClone 的脚本在《SQL Server 登陆账户权限克隆》一文中，直接Copy执行就好。

--在生产服务器创建
--1. 创建存储过程sp_DBA_LoginPermissionsClone
USE master
GO

--创建链接服务器
EXEC master.dbo.sp_addlinkedserver @server = N'LINK_BAKServer'
, @srvproduct=N'BAKServer', @provider=N'SQLNCLI', @datasrc=N'192.168.0.2,1433'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LINK_BAKServer'
,@useself=N'False',@locallogin=NULL,@rmtuser=N'link_loginClone',@rmtpassword='passWord'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_BAKServer', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_BAKServer', @optname=N'rpc out', @optvalue=N'true'
GO
--创建存储过程
CREATE PROC dbo.dba_loginReplication
AS
BEGIN
      SET NOCOUNT ON
      DECLARE @loginName sysname
      DECLARE @sql VARCHAR(max)
      DECLARE cur CURSOR FOR
      SELECT name
      from sys.sql_logins
      WHERE name NOT IN('sa','##MS_PolicyTsqlExecutionLogin##','##MS_PolicyEventProcessingLogin##')
      --在查询条件排除你认为不需要的账户

      OPEN cur
      FETCH NEXT FROM cur INTO @loginName
      WHILE @@FETCH_STATUS>=0
      BEGIN
            EXEC sp_DBA_LoginPermissionsClone      @loginName,NULL,NULL,@sql OUTPUT
            EXEC LINK_BAKServer.master.dbo.dba_loginPermissionSQL_Insert @sql
            FETCH NEXT FROM cur INTO @loginName
      END
      CLOSE cur
      DEALLOCATE cur
      
      --清除历史数据
      EXEC LINK_BAKServer.master.dbo.dba_loginPermissionSQL_Delete
END
GO

最后在生产服务器上创建一个作业，定期执行存储过程 dba_loginReplication 。当然这个周期不需要太频繁，登陆账户变化频繁的，可以一天执行一次，登陆账户不怎么变动的，可以一周执行一次。具体根据实际情况来定。