1.Sp_hexadecimal Stored Procedure and Linked Server is must to run the Sync job Successfully .
2.Create Job in Mirror or Secondary Servers
3.After creating the SQL job go to steps and change to your Principal Linked Server Name in Step
Create below Hexadecimal Stored procedure on Mirror Server :
Stored Procedure:
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_hexadecimal] Script Date: 1/22/2018 10:50:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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
GOAfter executing above stored procedure create a Linked Server between PRINCIPLE and MIRROR Instances
And then use below script to create a SQL job to run for every week or depends on your environment:
SQL JOB :
USE [msdb]
GO
/****** Object: Job [_createLoginsFromPrimary] Script Date: 1/22/2018 10:52:46 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 1/22/2018 10:52:46 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'_createLoginsFromPrimary',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [...] Script Date: 1/22/2018 10:52:46 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'...',
@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'SET NOCOUNT ON
declare @samesids bit,@printonly bit
set @samesids=1
set @printonly=0
CREATE TABLE #Logins
(
loginId int IDENTITY(1, 1) NOT NULL,
loginName nvarchar(128) NOT NULL,
passwordHash varbinary(256) NULL,
sid varbinary(85) NOT NULL
)
-- openquery is used so that loginproperty function runs on the remote server,
-- otherwise we get back null
INSERT INTO #Logins(loginName, passwordHash, sid)
SELECT *
FROM OPENQUERY([DESKTOP-02JIB76\SQL1], '' -- Principle Linked Server Name
SELECT name, CONVERT(varbinary(256), LOGINPROPERTY(name, ''''PasswordHash'''')), sid
FROM master.sys.server_principals
WHERE type = ''''S''''
AND name NOT IN (''''sa'''', ''''guest'''')
AND create_date >= ''''12/31/1900''''
ORDER BY name'')
DECLARE
@count int, @loginId int, @loginName nvarchar(128),
@passwordHashOld varbinary(256), @passwordHashNew varbinary(256),
@sid varbinary(85), @sql nvarchar(4000), @password varchar(514),@sidparm varchar(100),@sidh varbinary(85)
--select * from #logins
SELECT @loginId = 1, @count = COUNT(*) FROM #Logins
print ''--sql login count:''+convert(varchar,@count)
WHILE @loginId <= @count BEGIN SELECT @loginName = loginName, @passwordHashNew = passwordHash, @sid = sid FROM #Logins WHERE loginId = @loginId if @samesids=1 set @sidparm='',SID = '' + master.sys.fn_varbintohexstr(@SID) else set @sidparm='''' EXEC master.dbo.sp_hexadecimal @passwordHashNew, @password OUTPUT -- if the account doesn''t exist, then we need to create it IF NOT EXISTS (SELECT * FROM master.sys.server_principals WHERE name=@loginName) BEGIN set @sql=''-- login does not exist on target:''+char(13) SET @sql = @sql + ''CREATE LOGIN ['' + @loginName + ''] WITH PASSWORD = '' SET @sql = @sql + CONVERT(nvarchar(512), COALESCE(@password, ''NULL'')) SET @sql = @sql + '' HASHED, CHECK_POLICY = OFF''+@sidparm print @sql if @printonly=0 exec (@sql) END -- if the account does exist, then we need to drop/create to sync the password; -- can''t alter as hashed isn''t supported ELSE BEGIN SELECT @passwordHashOld = CONVERT(varbinary(256), LOGINPROPERTY(@loginName, ''PasswordHash'')) -- only update if the password has changed since the last sync or sid is different: IF (@passwordHashOld <> @passwordHashNew)
OR EXISTS (SELECT * FROM master.sys.server_principals WHERE name=@loginName
and sid<>@sid and @samesids=1)
BEGIN
if (@passwordHashOld <> @passwordHashNew)
set @sql=''-- exists on target, but password mismatch:''+char(13)
else
set @sql=''-- exists on target, but sid mismatch:''+char(13)
SET @sql = @sql+''DROP LOGIN ['' + @loginName+'']''
print @sql
if @printonly=0
exec (@sql)
SET @sql = ''CREATE LOGIN ['' + @loginName + ''] WITH PASSWORD = ''
SET @sql = @sql + CONVERT(nvarchar(512), COALESCE(@password, ''NULL''))
SET @sql = @sql + '' HASHED, CHECK_POLICY = OFF''+@sidparm
print @sql
if @printonly=0
exec (@sql)
END
END
SET @loginId = @loginId + 1
END
DROP TABLE #Logins',
@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'weekly',
@enabled=1,
@freq_type=8,
@freq_interval=17,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20110722,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'a3c0b837-988a-4a15-8fa5-30eb70feae17'
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