Sync logins/users between Principal/ Mirror/ Alwayon Servers

There are many ways to transfer SQL logins between the SQL Server instances and also I believe all of the transfer logins methods have some issues . 

But I used below Script which worked fine without any issues.

1. First create Stored Procedure Hexadecimal on your sql instance.

If there is a Mirroring create linked Servers between principle and Mirror Instance.
If there is Always on create linked Servers between Primary and Secondary Instances .

The script depends on sp_hexadecimal.  Please find the script below :

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

GO


2 . Put below script in SQL Job :


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\SQL1], '--Change this to your source Instance 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


Note :
"LinkedServerName ( DESKTOP\SQL1 )" in the OPENQUERY statement needs to be changed to the linked server that points to the source server in above script .