How To Migrate MSSQL Server Logins To Another Instance

Engr. Mohammad Rizwan Yasin

6 years ago

Step 1: Create SP sp_help_revlogin on master database of Source / Primary instance

The following script create two stored-procedures. You will use the first one when you want to transfer logins.

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE 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

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR

SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR

SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

USE master

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL

DROP PROCEDURE sp_hexadecimal

CREATE PROCEDURE sp_hexadecimal

@binvalue varbinary(256),

@hexvalue varchar (514) OUTPUT

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

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL

DROP PROCEDURE sp_help_revlogin

CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS

DECLARE @name sysname

DECLARE @type varchar (1)

DECLARE @hasaccess int

DECLARE @denylogin int

DECLARE @is_disabled int

DECLARE @PWD_varbinary varbinary (256)

DECLARE @PWD_string varchar (514)

DECLARE @SID_varbinary varbinary (85)

DECLARE @SID_string varchar (514)

DECLARE @tmpstr varchar (1024)

DECLARE @is_policy_checked varchar (3)

DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)

DECLARE login_curs CURSOR FOR

SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM

sys.server_principals p LEFT JOIN sys.syslogins l

ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'

ELSE

DECLARE login_curs CURSOR FOR

SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM

sys.server_principals p LEFT JOIN sys.syslogins l

ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

IF (@@fetch_status = -1)

BEGIN

PRINT 'No login(s) found.'

CLOSE login_curs

DEALLOCATE login_curs

RETURN -1

END

SET @tmpstr = '/* sp_help_revlogin script '

PRINT @tmpstr

SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

PRINT @tmpstr

PRINT ''

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

PRINT ''

SET @tmpstr = '-- Login: ' + @name

PRINT @tmpstr

IF (@type IN ( 'G', 'U'))

BEGIN -- NT authenticated account/group

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'

END

ELSE BEGIN -- SQL Server authentication

-- obtain password and sid

SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )

EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT

EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

-- obtain password policy state

SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

IF ( @is_policy_checked IS NOT NULL )

BEGIN

SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked

END

IF ( @is_expiration_checked IS NOT NULL )

BEGIN

SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked

END

IF (@denylogin = 1)

BEGIN -- login is denied access

SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )

END

ELSE IF (@hasaccess = 0)

BEGIN -- login exists but does not have access

SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )

END

IF (@is_disabled = 1)

BEGIN -- login is disabled

SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'

END

PRINT @tmpstr

END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

https://support.microsoft.com/en-us/kb/246133

This script creates two stored procedures in your master database named sp_help_revlogin and sp_hexadecimal.

Step 2: Execute the SP sp_help_revlogin on Source Instance

This gives you the scripts of the Login’s you want to carry in encrypted form. You can run it on the target server by copying the part of the login you want to move from the script.

Step 3: Drop Role Members & Users on target machine

Execute the following 2 queries on target machine to extract the members of roles and users in the current database

3a) Query1

SELECT 'sp_droprolemember ' +''''+ roleprinc.[name] + ''',''' + memberprinc.[name] +'''
GO'
FROM sys.database_role_members members JOIN sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
inner join sys.syslogins s on s.name=memberprinc.name LEFT JOIN sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]

SELECT 'sp_droprolemember ' +''''+ roleprinc.[name] + ''',''' + memberprinc.[name] +'''

GO'

FROM sys.database_role_members members JOIN sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]

JOIN sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]

inner join sys.syslogins s on s.name=memberprinc.name LEFT JOIN sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]

3b) Query2

 select' DROP USER '+ name +'
GO' as nname from sys.sysusers where altuid is null and LEN(sid)>5

1 2	select' DROP USER '+ name +' GO' as nname from sys.sysusers where altuid is null and LEN(sid)>5

3c) Drop Role Members & Users

Extract the output of the above 2 queries and execute on the same target machine to drop role members and users from the database accordingly.

Step 4: Add Users & Role Members on target machine

Execute the following 2 queries on source/production instance to extract the members of roles and users in the current database

4a) Query1

select 'CREATE USER '+name+ ' FOR LOGIN '+ name +'
GO' from sys.syslogins where password is not null

1 2	select 'CREATE USER '+name+ ' FOR LOGIN '+ name +' GO' from sys.syslogins where password is not null

4b) Query2

--------------------------ADD USERS TO THE ROLES FOR THAT Particular database----------------------------
SELECT 'sp_addrolemember ' +''''+ roleprinc.[name] + ''',''' + memberprinc.[name] +'''
GO' FROM
--Role/member associations
sys.database_role_members members
JOIN
--Roles
sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
--inner join sys.syslogins s on s.name=roleprinc.name
JOIN
--Role members (database users)
sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
inner join sys.syslogins s on s.name=memberprinc.name
LEFT JOIN
--Login accounts
sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]

--------------------------ADD USERS TO THE ROLES FOR THAT Particular database----------------------------

SELECT 'sp_addrolemember ' +''''+ roleprinc.[name] + ''',''' + memberprinc.[name] +'''

GO' FROM

--Role/member associations

sys.database_role_members members

JOIN

--Roles

sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]

--inner join sys.syslogins s on s.name=roleprinc.name

JOIN

--Role members (database users)

sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]

inner join sys.syslogins s on s.name=memberprinc.name

LEFT JOIN

--Login accounts

sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]

4c) Add Role Members & Users

Extract the output of the above 2 queries and execute on the same target machine to drop role members and users from the database accordingly.

Step 5: Grant Permission to Users for specific Objects

Execute the following script on source / production server to extract the permissions on objects.

SELECT perm.state_desc + ' ' +perm.[permission_name] + ' ON '+ OBJECT_NAME(perm.major_id) +' TO ', princ.[name] as aa
FROM 
--database user
sys.database_principals princ inner join sys.syslogins s on s.name=princ.name
left JOIN
--Login accounts
sys.login_token ulogin on princ.[sid] = ulogin.[sid]
inner JOIN 
--Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]

left JOIN
sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE 
princ.[type] in ('S','U')
and obj.type_desc is not null

SELECT perm.state_desc + ' ' +perm.[permission_name] + ' ON '+ OBJECT_NAME(perm.major_id) +' TO ', princ.[name] as aa

FROM

--database user

sys.database_principals princ inner join sys.syslogins s on s.name=princ.name

left JOIN

--Login accounts

sys.login_token ulogin on princ.[sid] = ulogin.[sid]

inner JOIN

--Permissions

sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]

left JOIN

sys.objects obj ON perm.[major_id] = obj.[object_id]

WHERE

princ.[type] in ('S','U')

and obj.type_desc is not null

Export the output generated from this query and execute it on the target server to assign the relevant permissions.

CHECK & Verify Login Access and the permissions of database objects and their specific roles respectively.