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 .
