SQL Server实例间同步登录用户
SQL Server实例间同步登录用户
问题痛点：由于AlwaysOn和数据库镜像无法同步数据库外实例对象，例如 登录用户、作业、链接服务器等，导致主库切换之后，应用连接不上数据库或者作业不存在导致每晚跑批任务漏跑等
目前来看，作业等其他实例对象的同步还比较难实现，比如作业分为很多步骤，而且作业包含的命令也比较复杂，作业也支持调用其他子系统，比如 PowerShell ，ActiveX，CmdExec等数据库外部程序和命令，用动态SQL方式很难处理
目前只有升级到SQL Server 2022并使用包含可用性组（支持同步登录用户、SQL代理作业、链接服务器）才能最丝滑的解决这个问题
本文主要介绍的是登录用户的同步，毕竟登录用户的重要性还是比较高的，应用需要先通过登录用户登录DB实例才能执行后续的操作
要在SQLServer实例间同步登录用户，主要有几种方法
1、创建操作系统域用户，然后创建基于这个域用户的登录用户，因为域用户在域里面是同步的，但是这种方法前提是需要有域环境，而且普通开发人员一般也没有域控机器权限创建域用户
2、使用外部第三方工具，比如 sqlcmd，PowerShell，SQLServer自带的SSIS服务
3、使用链接服务器 和 动态拼接SQL方法
本文主要使用第三种方法，因为第三种方法本人认为有下面几种优势
1、保证最低维护成本，纯SQL实现，不需要借助第三方工具
2、通用性，几乎所有SQL Server版本都能用，也不需要像第三方工具例如 PowerShell那样有时候需要升级版本
3、兼容性，跨操作系统平台Linux、Windows
4、高可靠性，使用SQLServer自带原生工具，足够简单高效
这个工具脚本的主要流程如下
这个工具脚本的架构图
具体使用步骤
假设有三个AlwaysOn节点，分别是
node1 ip：192.168.10.10
node2 ip：192.168.10.11
node3 ip：192.168.10.12
step1： 创建链接服务器，在所有AlwaysOn节点上创建其他节点的链接服务器，比如在192.168.10.10上创建其他节点链接服务器，下面脚本在192.168.10.10服务器上执行，其他节点以此类推
--create linkedserver USE [master] GO DECLARE @IP NVARCHAR(MAX) DECLARE @Login NVARCHAR(MAX) DECLARE @PWD NVARCHAR(MAX) SET @Login = N'sa' --★Do SET @PWD = N'xxxxxx' --★Do SET @IP ='192.168.10.11,1433' --★Do EXEC master.dbo.sp_addlinkedserver @server = @IP,@srvproduct = N'SQL Server' EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'collation compatible', @optvalue = N'false' EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'data access', @optvalue = N'true' EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'dist',@optvalue = N'false' EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'pub',@optvalue = N'false' EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'rpc',@optvalue = N'true' EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'rpc out',@optvalue = N'true' EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'sub',@optvalue = N'false' EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'connect timeout', @optvalue = N'0' EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'collation name', @optvalue = NULL EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'lazy schema validation', @optvalue = N'false' EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'query timeout', @optvalue = N'0' EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'use remote collation', @optvalue = N'true' EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'connect timeout', @optvalue = N'120' EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'query timeout', @optvalue = N'120' EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'remote proc transaction promotion',@optvalue = N'true' USE [master] EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = @IP, @locallogin = NULL, @useself = N'False', @rmtuser = @Login, @rmtpassword = @PWD --------------------------------------------------------------------------------------------------------------------------- --create linkedserver USE [master] GO DECLARE @IP NVARCHAR(MAX) DECLARE @Login NVARCHAR(MAX) DECLARE @PWD NVARCHAR(MAX) SET @Login = N'sa' --★Do SET @PWD = N'xxxxxx' --★Do SET @IP ='192.168.10.12,1433' --★Do EXEC master.dbo.sp_addlinkedserver @server = @IP,@srvproduct = N'SQL Server' EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'collation compatible', @optvalue = N'false' EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'data access', @optvalue = N'true' EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'dist',@optvalue = N'false' EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'pub',@optvalue = N'false' EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'rpc',@optvalue = N'true' EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'rpc out',@optvalue = N'true' EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'sub',@optvalue = N'false' EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'connect timeout', @optvalue = N'0' EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'collation name', @optvalue = NULL EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'lazy schema validation', @optvalue = N'false' EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'query timeout', @optvalue = N'0' EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'use remote collation', @optvalue = N'true' EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'connect timeout', @optvalue = N'120' EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'query timeout', @optvalue = N'120' EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'remote proc transaction promotion',@optvalue = N'true' USE [master] EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = @IP, @locallogin = NULL, @useself = N'False', @rmtuser = @Login, @rmtpassword = @PWD
step2： 创建存储过程，在所有AlwaysOn节点上创建存储过程，记住是所有AlwaysOn节点都要执行
USE [master] GO -- ================================================================= -- Author: <steven> -- Create date: <2021-12-26> -- Description: <Synchronize login users between multiple SQLServer Instances> -- ================================================================= create PROCEDURE [dbo].[usp_SyncLoginUserRegularBetweenInstances] AS BEGIN IF EXISTS(SELECT 1 FROM sys.dm_hadr_availability_replica_states hars INNER JOIN sys.availability_groups ag ON ag.group_id = hars.group_id INNER JOIN sys.availability_replicas ar ON ar.replica_id = hars.replica_id WHERE [hars].[is_local] = 1 AND [hars].[role_desc] = 'PRIMARY'AND [hars].[operational_state_desc] = 'ONLINE' AND [hars].[synchronization_health_desc] = 'HEALTHY') BEGIN ----Check for prerequisite, if not present deploy it. IF NOT EXISTS (SELECT id FROM [master].[dbo].[sysobjects] where name='sp_hexadecimal' and xtype='P') BEGIN DECLARE @sp_hexadecimalcreatescript NVARCHAR(3000) --The sp_hexadecimal stored procedure is used to generate the user's password hash value and the user's SID SET @sp_hexadecimalcreatescript = N' CREATE PROCEDURE [dbo].[sp_hexadecimal] @binvalue VARBINARY(256) , @hexvalue VARCHAR(514) OUTPUT AS DECLARE @charvalue VARCHAR(514); DECLARE @i INT; DECLARE @length INT; DECLARE @hexstring CHAR(16); SELECT @charvalue = ''0x''; SELECT @i = 1; SELECT @length = DATALENGTH(@binvalue); SELECT @hexstring = ''0123456789ABCDEF''; WHILE ( @i <= @length ) BEGIN DECLARE @tempint INT; DECLARE @firstint INT; DECLARE @secondint INT; SELECT @tempint = CONVERT(INT, SUBSTRING(@binvalue, @i, 1)); SELECT @firstint = FLOOR(@tempint / 16); SELECT @secondint = @tempint - ( @firstint * 16 ); SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint + 1, 1) + SUBSTRING(@hexstring, @secondint + 1, 1); SELECT @i = @i + 1; END; SELECT @hexvalue = @charvalue;' EXEC [master].[dbo].sp_executesql @sp_hexadecimalcreatescript END --The temporary table below is used to save the generated login user script , user by user, line by line DECLARE @TempTable TABLE (id INT IDENTITY ,Script NVARCHAR(MAX)) DECLARE @Login NVARCHAR(MAX) DECLARE CURLOGIN CURSOR FOR SELECT name FROM sys.server_principals WHERE [type] = 'S' AND [is_disabled] =0 AND [name] <> 'sa' --WHERE CONVERT(VARCHAR(24), create_date, 103) = CONVERT(VARCHAR(24), GETDATE(), 103) -- OR CONVERT(VARCHAR(24), modify_date, 103) = CONVERT(VARCHAR(24), GETDATE(), 103) OPEN CURLOGIN FETCH NEXT FROM CURLOGIN INTO @Login WHILE @@FETCH_STATUS = 0 BEGIN SET NOCOUNT ON DECLARE @Script NVARCHAR(MAX) DECLARE @LoginName VARCHAR(1500) = @Login DECLARE @LoginSID VARBINARY(400) DECLARE @SID_String VARCHAR(1514) DECLARE @LoginPWD VARBINARY(1256) DECLARE @PWD_String VARCHAR(1514) DECLARE @LoginType CHAR(1) DECLARE @is_disabled BIT DECLARE @default_database_name SYSNAME DECLARE @default_language_name SYSNAME DECLARE @is_policy_checked BIT DECLARE @is_expiration_checked BIT DECLARE @createdDateTime DATETIME SELECT @LoginSID = P.[sid] , @LoginType = P.[type] , @is_disabled = P.is_disabled , @default_database_name = P.default_database_name , @default_language_name = P.default_language_name , @createdDateTime = P.create_date FROM sys.server_principals P WHERE P.name = @LoginName SET @Script = '' --If the login is a SQL Login, then do a lot of stuff... IF @LoginType = 'S' BEGIN SET @LoginPWD = CAST(LOGINPROPERTY(@LoginName, 'PasswordHash') AS VARBINARY(256)) EXEC [master].[dbo].[sp_hexadecimal] @LoginPWD, @PWD_String OUT EXEC [master].[dbo].[sp_hexadecimal] @LoginSID, @SID_String OUT SELECT @is_policy_checked = S.is_policy_checked , @is_expiration_checked = S.is_expiration_checked FROM sys.sql_logins S WHERE S.[type] = 'S' AND S.[is_disabled] =0 -- Create diff Script SET @Script = @Script + CHAR(13) + CHAR(13) + '''' + CHAR(13) + 'USE [master];' + CHAR(13) + 'IF EXISTS (SELECT name FROM sys.server_principals WHERE name= ''''' + @LoginName + ''''') ' + CHAR(13) + 'BEGIN ' + CHAR(13) + CHAR(9) + ' DECLARE @CurrentLoginPWD VARBINARY(512)' + CHAR(13) + CHAR(9) + ' DECLARE @CurrentPWD_String VARCHAR(1514)' + CHAR(13) + CHAR(9) + ' DECLARE @CurrentLoginSID VARBINARY(400)' + CHAR(13) + CHAR(9) + ' DECLARE @CurrentSID_String VARCHAR(1514)' + CHAR(13) + CHAR(9) + ' SELECT @CurrentLoginSID = [sid] FROM sys.server_principals WHERE name = '''''+ @LoginName +'''''' + CHAR(13) + CHAR(9) + ' SET @CurrentLoginPWD =CAST(LOGINPROPERTY(''''' + @LoginName + ''''', ' + '''''PasswordHash''''' +') AS VARBINARY(512))' + CHAR(13) + CHAR(9) + ' EXEC [master].[dbo].[sp_hexadecimal] @CurrentLoginPWD , @CurrentPWD_String OUT ' + CHAR(13) + CHAR(9) + ' EXEC [master].[dbo].[sp_hexadecimal] @CurrentLoginSID, @CurrentSID_String OUT ' + CHAR(13) + CHAR(9) + ' --Compare two SID if the same ' + CHAR(13) + CHAR(9) + ' IF ''''' + @SID_String + ''''' = @CurrentSID_String ' + CHAR(13) + CHAR(9) + ' BEGIN' + CHAR(13) + CHAR(9) + CHAR(9) + CHAR(9) + ' --Compare two password if the same ' + CHAR(13) + CHAR(9) + CHAR(9) + CHAR(9) + ' IF ''''' + @PWD_String + ''''' <> @CurrentPWD_String ' + CHAR(13) + CHAR(9) + CHAR(9) + CHAR(9) + ' BEGIN' + CHAR(13) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + '--Just update login user password' + CHAR(13) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + ' ALTER LOGIN ' + QUOTENAME(@LoginName) + CHAR(13) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + ' WITH PASSWORD = ' + @PWD_String + ' HASHED' + CHAR(13) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + ', DEFAULT_DATABASE = [' + @default_database_name + ']' + CHAR(13) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + ', DEFAULT_LANGUAGE = [' + @default_language_name + ']' + CHAR(13) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + ', CHECK_POLICY ' + CASE WHEN @is_policy_checked = 0 THEN '=OFF' ELSE '=ON' END + CHAR(13) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + ', CHECK_EXPIRATION ' + CASE WHEN @is_expiration_checked = 0 THEN '=OFF' ELSE '=ON' END + CHAR(13) + CHAR(9) + CHAR(9) + CHAR(9) + ' END' + CHAR(13) + CHAR(9) + ' END' + CHAR(13) + 'END ' + CHAR(13) + 'ELSE' + CHAR(13) + 'BEGIN ' + CHAR(13) + CHAR(9) + ' --Create new login user ' + CHAR(13) + CHAR(9) + ' CREATE LOGIN ' + QUOTENAME(@LoginName) + CHAR(13) + CHAR(9) + ' WITH PASSWORD = ' + @PWD_String + ' HASHED' + CHAR(13) + CHAR(9) + ', SID = ' + @SID_String + CHAR(13) + CHAR(9) + ', DEFAULT_DATABASE = [' + @default_database_name + ']' + CHAR(13) + CHAR(9) + ', DEFAULT_LANGUAGE = [' + @default_language_name + ']' + CHAR(13) + CHAR(9) + ', CHECK_POLICY ' + CASE WHEN @is_policy_checked = 0 THEN '=OFF' ELSE '=ON' END + CHAR(13) + CHAR(9) + ', CHECK_EXPIRATION ' + CASE WHEN @is_expiration_checked = 0 THEN '=OFF' ELSE '=ON' END + CHAR(13) + 'END ' --SET @Script = @Script + CHAR(13) + CHAR(13) -- + ' ALTER LOGIN [' + @LoginName + ']' -- + CHAR(13) + CHAR(9) + 'WITH DEFAULT_DATABASE = [' + @default_database_name + ']' -- + CHAR(13) + CHAR(9) + ', DEFAULT_LANGUAGE = [' + @default_language_name + ']' END --ELSE --BEGIN -- --The login is a NT login (or group). -- SET @Script = @Script + CHAR(13) + CHAR(13) -- + 'IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name= ''' + @LoginName + ''') ' -- + CHAR(13) + ' BEGIN ' -- + CHAR(13) + CHAR(9) + ' CREATE LOGIN ' + QUOTENAME(@LoginName) + ' FROM WINDOWS' -- + CHAR(13) + CHAR(9) + 'WITH DEFAULT_DATABASE = [' + @default_database_name + ']' -- + CHAR(13) + ' END ' --END --This section deals with the Server Roles that belong to that login... DECLARE @ServerRoles TABLE ( ServerRole SYSNAME , MemberName SYSNAME , MemberSID VARBINARY(185) ) ----Prevent multiple records from being inserted into the @ServerRoles table IF NOT EXISTS (SELECT 1 FROM @ServerRoles ) BEGIN INSERT INTO @ServerRoles EXEC sp_helpsrvrolemember END ----Remove all Roles --SET @Script = @Script + CHAR(13) --SET @Script = @Script -- + CHAR(13) + 'EXEC sp_dropsrvrolemember ' + QUOTENAME(@LoginName) + ', ''sysadmin''' -- + CHAR(13) + 'EXEC sp_dropsrvrolemember ' + QUOTENAME(@LoginName) + ', ''securityadmin''' -- + CHAR(13) + 'EXEC sp_dropsrvrolemember ' + QUOTENAME(@LoginName) + ', ''serveradmin''' -- + CHAR(13) + 'EXEC sp_dropsrvrolemember ' + QUOTENAME(@LoginName) + ', ''setupadmin''' -- + CHAR(13) + 'EXEC sp_dropsrvrolemember ' + QUOTENAME(@LoginName) + ', ''processadmin''' -- + CHAR(13) + 'EXEC sp_dropsrvrolemember ' + QUOTENAME(@LoginName) + ', ''diskadmin''' -- + CHAR(13) + 'EXEC sp_dropsrvrolemember ' + QUOTENAME(@LoginName) + ', ''dbcreator''' -- + CHAR(13) + 'EXEC sp_dropsrvrolemember ' + QUOTENAME(@LoginName) + ', ''bulkadmin''' /** Output to script... **/ --SET @Script = @Script + CHAR(13) + CHAR(13) --Test if there are any server roles for this login... IF EXISTS(SELECT 1 FROM @ServerRoles WHERE MemberName = @LoginName) BEGIN SET @Script = @Script + CHAR(13) DECLARE @ServerRole SYSNAME DECLARE curRoles CURSOR LOCAL STATIC FORWARD_ONLY FOR SELECT ServerRole FROM @ServerRoles WHERE MemberName = @LoginName OPEN curRoles FETCH NEXT FROM curRoles INTO @ServerRole WHILE @@FETCH_STATUS = 0 BEGIN /** Output to Script **/ SET @Script = @Script + CHAR(13) + 'EXEC sp_addsrvrolemember ' + QUOTENAME(@LoginName) + ', ' + '''''' + @ServerRole + '''''' FETCH NEXT FROM curRoles INTO @ServerRole END --Cleanup. CLOSE curRoles DEALLOCATE curRoles END SET @Script = @Script + CHAR(13) + '''' INSERT INTO @TempTable VALUES(@Script) FETCH NEXT FROM CURLOGIN INTO @Login END CLOSE CURLOGIN; DEALLOCATE CURLOGIN; SELECT id, Script FROM @TempTable ORDER BY id ------------------------------------------------------------------------------------ --Use linked servers to send scripts to remote machines for execution -------------------------------------------------------------------------------- DECLARE @LinkedServerName NVARCHAR(512); DECLARE @DynamicSQL NVARCHAR(MAX); DECLARE @EXISTSSQL NVARCHAR(2000); DECLARE cursor_linked_servers CURSOR FOR SELECT name FROM sys.servers WHERE is_linked = 1 AND [product]='SQL Server' AND [provider]='SQLNCLI' AND [connect_timeout]>0 AND [query_timeout] >0; OPEN cursor_linked_servers; FETCH NEXT FROM cursor_linked_servers INTO @LinkedServerName; WHILE @@FETCH_STATUS = 0 BEGIN --Determine whether the remote machine has the stored procedure call "sp_hexadecimal" --if not have do not execute the cursor traversal loop CREATE TABLE #EXISTSTB(id BIGINT) SET @EXISTSSQL='SELECT * FROM OPENQUERY('+ QUOTENAME(@LinkedServerName) + ', ''SELECT id FROM [master].[dbo].[sysobjects] WHERE name = ''''sp_hexadecimal'''' AND xtype=''''P'''' '')' INSERT INTO #EXISTSTB EXEC(@EXISTSSQL) IF EXISTS(SELECT * FROM #EXISTSTB) BEGIN DECLARE @RunSQL NVARCHAR(MAX) DECLARE CURSYNC CURSOR FOR SELECT Script FROM @TempTable ORDER BY id OPEN CURSYNC FETCH NEXT FROM CURSYNC INTO @RunSQL WHILE @@FETCH_STATUS = 0 BEGIN SET @DynamicSQL = 'EXEC('+ @RunSQL + ') AT ['+ @LinkedServerName +']' EXEC sp_executesql @DynamicSQL; FETCH NEXT FROM CURSYNC INTO @RunSQL END; CLOSE CURSYNC DEALLOCATE CURSYNC END DROP TABLE #EXISTSTB FETCH NEXT FROM cursor_linked_servers INTO @LinkedServerName; END; -- close cursor CLOSE cursor_linked_servers; DEALLOCATE cursor_linked_servers; END END
存储过程逻辑解析
--关键代码模块解析 1. 检查当前实例是否为 AlwaysOn集群的 主节点 IF EXISTS(SELECT 1 FROM sys.dm_hadr_availability_replica_states hars INNER JOIN sys.availability_groups ag ON ag.group_id = hars.group_id INNER JOIN sys.availability_replicas ar ON ar.replica_id = hars.replica_id WHERE hars.is_local = 1 AND hars.role_desc = 'PRIMARY' AND hars.operational_state_desc = 'ONLINE' AND hars.synchronization_health_desc = 'HEALTHY') BEGIN -- 主节点逻辑 END 作用：确保仅在主节点执行同步，避免多节点同时操作导致冲突。 判断条件： is_local = 1：当前实例是本地节点。 role_desc = 'PRIMARY'：角色为主节点。 状态均为在线且健康（OPERATIONAL_STATE_DESC = 'ONLINE'，SYNCHRONIZATION_HEALTH_DESC = 'HEALTHY'）。
--====================================================================
2. 创建 sp_hexadecimal 存储过程（若不存在） IF NOT EXISTS (SELECT id FROM [master].[dbo].[sysobjects] WHERE name='sp_hexadecimal' AND xtype='P') BEGIN DECLARE @sp_hexadecimalcreatescript NVARCHAR(3000) SET @sp_hexadecimalcreatescript = N'CREATE PROCEDURE [dbo].[sp_hexadecimal] ...' EXEC [master].[dbo].sp_executesql @sp_hexadecimalcreatescript END 作用：该存储过程用于将二进制值（如登录用户的 SID、密码哈希）转换为十六进制字符串，以便在跨实例同步时传输和比较。 原理：通过循环逐字节转换二进制数据为十六进制（如 0x1234AB 格式）。
--====================================================================
3. 遍历本地 SQL 登录用户并生成同步脚本，并把同步脚本存入临时表 DECLARE CURLOGIN CURSOR FOR SELECT name FROM sys.server_principals WHERE [type] = 'S' AND [is_disabled] = 0 AND [name] <> 'sa' OPEN CURLOGIN FETCH NEXT FROM CURLOGIN INTO @Login WHILE @@FETCH_STATUS = 0 BEGIN -- 提取登录用户属性（SID、密码哈希、默认数据库等） SELECT @LoginSID = P.[sid], @LoginType = P.[type], ... FROM sys.server_principals P WHERE P.name = @LoginName -- 仅处理 SQL方式的 登录用户（类型 'S'） IF @LoginType = 'S' BEGIN -- 获取密码哈希并转换为十六进制 SET @LoginPWD = CAST(LOGINPROPERTY(@LoginName, 'PasswordHash') AS VARBINARY(256)) EXEC [master].[dbo].[sp_hexadecimal] @LoginPWD, @PWD_String OUT EXEC [master].[dbo].[sp_hexadecimal] @LoginSID, @SID_String OUT -- 生成同步脚本：先检查当前实例中的登录用户是否存在，再更新或创建登录用户 SET @Script = 'USE [master]; IF EXISTS (SELECT name FROM sys.server_principals WHERE name=''' + @LoginName + ''') BEGIN ... -- 比较 SID 和密码，更新密码 END ELSE ... -- 创建新登录用户' END -- 处理服务器角色（如 sysadmin、dbcreator 等） DECLARE @ServerRoles TABLE (ServerRole SYSNAME, MemberName SYSNAME, MemberSID VARBINARY(185)) INSERT INTO @ServerRoles EXEC sp_helpsrvrolemember IF EXISTS(SELECT 1 FROM @ServerRoles WHERE MemberName = @LoginName) BEGIN DECLARE curRoles CURSOR FOR SELECT ServerRole FROM @ServerRoles WHERE MemberName = @LoginName OPEN curRoles FETCH NEXT FROM curRoles INTO @ServerRole WHILE @@FETCH_STATUS = 0 BEGIN SET @Script = @Script + 'EXEC sp_addsrvrolemember ' + QUOTENAME(@LoginName) + ', ''' + @ServerRole + '''' FETCH NEXT FROM curRoles INTO @ServerRole END END -- 将脚本存入临时表 INSERT INTO @TempTable VALUES(@Script) FETCH NEXT FROM CURLOGIN INTO @Login END 核心逻辑： 筛选用户：仅处理类型为 'S'（SQL 登录用户）、未禁用且非 sa 的用户。 提取属性：获取登录用户的 SID、密码哈希（通过 LOGINPROPERTY）、默认数据库、语言等。 生成同步脚本： 存在性检查：先判断远程实例是否已有该登录用户。 SID 对比：若 SID 相同但密码不同，则更新密码（使用 ALTER LOGIN ... WITH PASSWORD = ... HASHED）。 创建登录用户：若不存在，则创建新登录用户，包含 SID、密码、默认数据库等属性。 服务器角色同步：通过 sp_helpsrvrolemember 获取角色成员，生成 sp_addsrvrolemember 脚本添加角色。
--====================================================================
4. 通过链接服务器 在远程实例上执行同步脚本 实现把登录用户同步给远程实例 DECLARE cursor_linked_servers CURSOR FOR SELECT name FROM sys.servers WHERE is_linked = 1 AND [product]='SQL Server' AND [provider]='SQLNCLI' AND [connect_timeout]>0 AND [query_timeout] >0 OPEN cursor_linked_servers FETCH NEXT FROM cursor_linked_servers INTO @LinkedServerName WHILE @@FETCH_STATUS = 0 BEGIN -- 检查远程实例是否存在 sp_hexadecimal CREATE TABLE #EXISTSTB(id BIGINT) SET @EXISTSSQL = 'SELECT * FROM OPENQUERY(' + QUOTENAME(@LinkedServerName) + ', ''SELECT id FROM [master].[dbo].[sysobjects] WHERE name=''''sp_hexadecimal'''' AND xtype=''''P'''' '')' INSERT INTO #EXISTSTB EXEC(@EXISTSSQL) IF EXISTS(SELECT * FROM #EXISTSTB) BEGIN -- 遍历临时表里本地生成的脚本并在远程执行 DECLARE CURSYNC CURSOR FOR SELECT Script FROM @TempTable ORDER BY id OPEN CURSYNC FETCH NEXT FROM CURSYNC INTO @RunSQL WHILE @@FETCH_STATUS = 0 BEGIN SET @DynamicSQL = 'EXEC(' + @RunSQL + ') AT [' + @LinkedServerName + ']' EXEC sp_executesql @DynamicSQL FETCH NEXT FROM CURSYNC INTO @RunSQL END END DROP TABLE #EXISTSTB FETCH NEXT FROM cursor_linked_servers INTO @LinkedServerName END 作用：通过链接服务器将本地生成的登录用户脚本推送到远程实例执行，从而实现登录用户的同步。 关键步骤： 筛选链接服务器：仅处理类型为 SQL Server 且配置有效的链接服务器。 远程存储过程检查：先检查远程实例是否存在 sp_hexadecimal，确保脚本可执行。 脚本执行：使用 EXEC(... ) AT [LinkedServer] 语法在远程实例动态执行同步脚本。 --注意事项与优化点 仅同步 SQL 登录用户：代码中注释了 Windows 登录用户的处理逻辑（ELSE 分支），如需同步 Windows 登录用户需取消注释并调整逻辑。 权限要求： 主节点需有 sysadmin 权限（读取登录信息、创建存储过程）。 链接服务器需配置正确的登录映射，确保远程执行脚本的权限。 安全性： 密码以哈希形式传输，避免明文泄露。 使用 QUOTENAME 转义登录名，防止 SQL 注入。
step3： 创建作业定时执行上面的存储过程，在所有AlwaysOn节点上创建作业，记住是所有AlwaysOn节点都要执行，下面脚本默认是60分钟执行一次
USE [msdb] GO /****** Object: Job [synchronize_loginusers] Script Date: 2023/9/6 15:46:26 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [Database Maintenance] Script Date: 2023/9/6 15:46:26 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'synchronize_loginusers', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'Synchronize login users between SQL Server Instances', @category_name=N'Database Maintenance', @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [execute SyncLoginUsers script] Script Date: 2023/9/6 15:46:26 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'execute SyncLoginUsers script', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'exec [master].[dbo].[usp_SyncLoginUserRegularBetweenInstances] ', @database_name=N'master', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule SyncLoginUsers', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=4, @freq_subday_interval=60, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20110224, @active_end_date=99991231, @active_start_time=200, @active_end_time=235959, @schedule_uid=N'563258f6-0b3f-47bf-b9b3-2f597038cc38' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO
所有步骤完毕
对于这个自动定时同步登录用户脚本有以下说明
本工具脚本遵循的思想是，不做减法，只做加法，尽可能减少对线上生产环境影响，主要有下面几点：
（1）新增用户：如果从库不存在同步过来的用户，就新建用户
（2）更新用户：如果从库存在同步过来的用户，就会判断用户SID和用户密码是否一样，如果用户SID一样，密码不一样，就更新用户密码
（3）删除用户：如果从库存在同名用户，就不新建用户，否则新建用户，为了尽可能减少对线上生产环境影响，不做删除用户操作，所以如果从库存在同名用户并且用户SID不同，建议手动删除用户由脚本自动同步主库用户过来
无论是新增用户还是更新用户，都会执行添加服务器角色权限的步骤，如果同名用户已经存在当前服务器角色权限，那么再次执行添加服务器角色权限并不会有任何影响，而且本工具脚本并不会删除同名用户的服务器角色权限
最终目的：不做减法，只做加法，尽可能减少对线上生产环境影响
对于这个自动定时同步登录用户脚本有下面几个注意点
1、本工具脚本使用sa用户来创建链接服务器，所以不会同步sa用户，注意如果更改了sa用户密码，也要同步更改链接服务器密码，当然您也可以使用其他有足够权限的用户来创建链接服务器
2、新建登录用户的时候，一定要确保在主库上新建登录用户，否则可能会无法实施数据库角色权限或者无法同步该登录用户
3、新建登录用户时候，登录用户名不要带有特殊字符，例如单引号，否则差异脚本有可能不work
4、这个脚本是针对AlwaysOn集群，当然如果你是数据库镜像环境，你可以改一下代码，把判断AlwaysOn主库的部分改为判断镜像主库就可以了
5、如果对登录用户同步延迟有要求，那么可以修改作业执行频率，默认是60分钟执行一次
参考文章
https://maq.guru/synchronizing-sql-server-logins-in-an-always-on-availability-group/
https://www.jb51.net/article/282734.htm
本文版权归作者所有，未经作者同意不得转载。
浙公网安备 33010602011771号