SQL?Server實(shí)例間同步登錄用戶的操作代碼
更新時(shí)間：2023年09月12日 09:47:08 作者：樺仔
本文主要介紹的是登錄用戶的同步，畢竟登錄用戶的重要性還是比較高的，應(yīng)用需要先通過(guò)登錄用戶登錄DB實(shí)例才能執(zhí)行后續(xù)的操作，本文結(jié)合實(shí)例代碼給大家介紹的非常詳細(xì)，需要的朋友參考下吧
問(wèn)題痛點(diǎn)：由于AlwaysOn和數(shù)據(jù)庫(kù)鏡像無(wú)法同步數(shù)據(jù)庫(kù)外實(shí)例對(duì)象，例如 登錄用戶、作業(yè)、鏈接服務(wù)器等，導(dǎo)致主庫(kù)切換之后，應(yīng)用連接不上數(shù)據(jù)庫(kù)或者作業(yè)不存在導(dǎo)致每晚跑批任務(wù)漏跑等
目前來(lái)看，作業(yè)等其他實(shí)例對(duì)象的同步還比較難實(shí)現(xiàn)，比如作業(yè)分為很多步驟，而且作業(yè)包含的命令也比較復(fù)雜，作業(yè)也支持調(diào)用其他子系統(tǒng)，比如 PowerShell ，ActiveX，CmdExec等數(shù)據(jù)庫(kù)外部程序和命令，用動(dòng)態(tài)SQL方式很難處理
本文主要介紹的是登錄用戶的同步，畢竟登錄用戶的重要性還是比較高的，應(yīng)用需要先通過(guò)登錄用戶登錄DB實(shí)例才能執(zhí)行后續(xù)的操作
要在SQLServer實(shí)例間同步登錄用戶，主要有幾種方法
1、創(chuàng)建操作系統(tǒng)域用戶，然后創(chuàng)建基于這個(gè)域用戶的登錄用戶，因?yàn)橛蛴脩粼谟蚶锩媸峭降模沁@種方法前提是需要有域環(huán)境，而且普通開(kāi)發(fā)人員一般也沒(méi)有域控機(jī)器權(quán)限創(chuàng)建域用戶
2、使用外部第三方工具，比如 sqlcmd，PowerShell
3、使用鏈接服務(wù)器 和 動(dòng)態(tài)拼接SQL方法
本文主要使用第三種方法，因?yàn)榈谌N方法本人認(rèn)為有下面幾種優(yōu)勢(shì)
1、保證最低維護(hù)成本，純SQL實(shí)現(xiàn)，不需要借助第三方工具
2、通用性，幾乎所有SQL Server版本都能用，也不需要像第三方工具例如 PowerShell那樣有時(shí)候需要升級(jí)版本
3、兼容性，跨操作系統(tǒng)平臺(tái)Linux、Windows
4、高可靠性，使用SQLServer自帶原生工具，足夠簡(jiǎn)單高效
這個(gè)工具腳本的主要流程如下
具體使用步驟
假設(shè)有三個(gè)AlwaysOn節(jié)點(diǎn)，分別是
node1 ip：192.168.10.10
node2 ip：192.168.10.11
node3 ip：192.168.10.12
step1： 創(chuàng)建鏈接服務(wù)器，在所有AlwaysOn節(jié)點(diǎn)上創(chuàng)建其他節(jié)點(diǎn)的鏈接服務(wù)器，比如在192.168.10.10上創(chuàng)建其他節(jié)點(diǎn)鏈接服務(wù)器，下面腳本在192.168.10.10服務(wù)器上執(zhí)行，其他節(jié)點(diǎn)以此類推
--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： 創(chuàng)建存儲(chǔ)過(guò)程，在所有AlwaysOn節(jié)點(diǎn)上創(chuàng)建存儲(chǔ)過(guò)程，記住是所有AlwaysOn節(jié)點(diǎn)都要執(zhí)行
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)
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
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 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 scripts
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
ENDstep3： 創(chuàng)建作業(yè)定時(shí)執(zhí)行上面的存儲(chǔ)過(guò)程，在所有AlwaysOn節(jié)點(diǎn)上創(chuàng)建作業(yè)，記住是所有AlwaysOn節(jié)點(diǎn)都要執(zhí)行，下面腳本默認(rèn)是60分鐘執(zhí)行一次
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所有步驟完畢
對(duì)于這個(gè)自動(dòng)定時(shí)同步登錄用戶腳本有以下說(shuō)明
本工具腳本遵循的思想是，不做減法，只做加法，盡可能減少對(duì)線上生產(chǎn)環(huán)境影響，主要有下面幾點(diǎn)：
（1）新增用戶：如果從庫(kù)不存在同步過(guò)來(lái)的用戶，就新建用戶
（2）更新用戶：如果從庫(kù)存在同步過(guò)來(lái)的用戶，就會(huì)判斷用戶SID和用戶密碼是否一樣，如果用戶SID一樣，密碼不一樣，就更新用戶密碼
（3）刪除用戶：如果從庫(kù)存在同名用戶，就不新建用戶，否則新建用戶，為了盡可能減少對(duì)線上生產(chǎn)環(huán)境影響，不做刪除用戶操作，所以如果從庫(kù)存在同名用戶并且用戶SID不同，建議手動(dòng)刪除用戶由腳本自動(dòng)同步主庫(kù)用戶過(guò)來(lái)
無(wú)論是新增用戶還是更新用戶，都會(huì)執(zhí)行添加服務(wù)器角色權(quán)限的步驟，如果同名用戶已經(jīng)存在當(dāng)前服務(wù)器角色權(quán)限，那么再次執(zhí)行添加服務(wù)器角色權(quán)限并不會(huì)有任何影響，而且本工具腳本并不會(huì)刪除同名用戶的服務(wù)器角色權(quán)限
最終目的：不做減法，只做加法，盡可能減少對(duì)線上生產(chǎn)環(huán)境影響
對(duì)于這個(gè)自動(dòng)定時(shí)同步登錄用戶腳本有下面幾個(gè)注意點(diǎn)
1、本工具腳本使用sa用戶來(lái)創(chuàng)建鏈接服務(wù)器，所以不會(huì)同步sa用戶，注意如果更改了sa用戶密碼，也要同步更改鏈接服務(wù)器密碼，當(dāng)然您也可以使用其他有足夠權(quán)限的用戶來(lái)創(chuàng)建鏈接服務(wù)器
2、新建登錄用戶的時(shí)候，一定要確保在主庫(kù)上新建登錄用戶，否則可能會(huì)無(wú)法實(shí)施數(shù)據(jù)庫(kù)角色權(quán)限或者無(wú)法同步該登錄用戶
3、新建登錄用戶時(shí)候，登錄用戶名不要帶有特殊字符，例如單引號(hào)，否則差異腳本有可能不work
4、這個(gè)腳本是針對(duì)AlwaysOn集群，當(dāng)然如果你是數(shù)據(jù)庫(kù)鏡像環(huán)境，你可以改一下代碼，把判斷AlwaysOn主庫(kù)的部分改為判斷鏡像主庫(kù)就可以了
5、如果對(duì)登錄用戶同步延遲有要求，那么可以修改作業(yè)執(zhí)行頻率，默認(rèn)是60分鐘執(zhí)行一次
參考文章
https://maq.guru/synchronizing-sql-server-logins-in-an-always-on-availability-group/
到此這篇關(guān)于SQL Server實(shí)例間同步登錄用戶的文章就介紹到這了,更多相關(guān)SQL Server同步登錄用戶內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
必須會(huì)的SQL語(yǔ)句(七) 字符串函數(shù)、時(shí)間函數(shù)
這篇文章主要介紹了sqlserver中字符串函數(shù)、時(shí)間函數(shù)使用方法,需要的朋友可以參考下2015-01-01
SQL語(yǔ)句實(shí)現(xiàn)查詢SQL Server內(nèi)存使用狀況
這篇文章主要介紹了SQL語(yǔ)句實(shí)現(xiàn)查詢SQL Server內(nèi)存使用狀況,本文直接給出實(shí)現(xiàn)代碼,需要的朋友可以參考下2015-07-07
SQL Server中使用判斷語(yǔ)句（IF ELSE/CASE WHEN ）案例
這篇文章主要介紹了SQL Server中使用判斷語(yǔ)句（IF ELSE/CASE WHEN ）案例,本篇文章通過(guò)簡(jiǎn)要的案例,講解了該項(xiàng)技術(shù)的了解與使用,以下就是詳細(xì)內(nèi)容,需要的朋友可以參考下2021-07-07
SQL Server數(shù)據(jù)庫(kù)日志查看若已滿需要清理的三種解決方案
因?yàn)閿?shù)據(jù)量較大,對(duì)數(shù)據(jù)庫(kù)表操作比較多,日志的模式,又是完全模式,所以會(huì)產(chǎn)生較大的日志文件,那么怎么清理呢,本文給大家介紹了SQL Server數(shù)據(jù)庫(kù)日志查看若已滿需要清理的三種解決方案,需要的朋友可以參考下2024-04-04
下面小編就為大家分享一篇基于SSIS 事件的向上傳遞(詳解)，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2018-01-01
這篇文章主要為大家介紹了SQLServer索引和視圖，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下，希望能夠給你帶來(lái)幫助2022-01-01
sql?server如何去除數(shù)據(jù)中的一些無(wú)用的空格
這篇文章主要介紹了sql?server去除數(shù)據(jù)中的一些無(wú)用的空格,本文給大家提到了一些常用的函數(shù)，結(jié)合示例代碼給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2023-05-05
Sql?Server登錄錯(cuò)誤:18456的解決方案
Sql?Server?安裝后使用Sql?Server身份驗(yàn)證登錄,出現(xiàn)錯(cuò)誤：18456,本文小編給大家介紹了Sql?Server登錄錯(cuò)誤:18456的解決方案,如果遇到相同問(wèn)題的同學(xué)可以參考閱讀下2023-10-10
遠(yuǎn)程登陸SQL Server 2014數(shù)據(jù)庫(kù)的方法
這篇文章主要為大家詳細(xì)介紹了遠(yuǎn)程登陸SQL Server 2014數(shù)據(jù)庫(kù)的方法，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-08-08
SQL Server查詢數(shù)據(jù)庫(kù)中表使用空間信息實(shí)現(xiàn)腳本
這篇文章主要介紹了SQL Server查詢數(shù)據(jù)庫(kù)中表使用空間信息實(shí)現(xiàn)腳本,本文直接給出實(shí)現(xiàn)代碼,需要的朋友可以參考下2015-07-07