SQL Server实例间同步登录用户

技术分享 2年前 (2023-09-15) 0 999+

关注

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)                      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          END

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