SQL SERVER – Transfer The Logins and The Passwords Between Instances of SQL Server 2005

This question was asked to me by one of reader.

“I just upgraded my server with better hardware and newer operating system. How can I transfer the logins and the passwords between two of my SQL Server?”

I think Microsoft has wonderful documentation for this issue. kb 918992

I will briefly describe the solution here :
Run the script in Query Editor. It will generate the script of username and password in the windows.
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(256) OUTPUT
AS
DECLARE
@charvalue varchar(256)
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 @xstatus int
DECLARE
@binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT
sid, name, xstatus, password
FROM master..sysxlogins
WHERE srvid IS NULL
AND
name <> 'sa'
ELSE
DECLARE
login_curs CURSOR FOR
SELECT
sid, name, xstatus, password
FROM master..sysxlogins
WHERE srvid IS NULL
AND
name = @login_name
OPEN login_curs
FETCH NEXT
FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
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 ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF
(@@fetch_status <> -2)
BEGIN
PRINT
''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE
BEGIN
-- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END
END
ELSE
BEGIN
-- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET
@tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE
BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF
(@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
SET
@tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
END
FETCH
NEXT
FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE
login_curs
DEALLOCATE login_curs
RETURN 0
GO
----- End Script -----
EXEC master..sp_help_revlogin
GO

Now copy the generated script and run it on other SQL Server where you want to move username and password. Make sure that you are logged with sysadmin role.

Reference : Pinal Dave (https://blog.sqlauthority.com) , kb 918992

Solarwinds
, , ,
Previous Post
SQL SERVER – Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial
Next Post
SQL SERVER – How to Retrieve TOP and BOTTOM Rows Together using T-SQL

Related Posts

41 Comments. Leave new

  • HI Pinal sir,

    does the script work for SQL logins also?

    Reply
  • Msg 208, Level 16, State 1, Procedure sp_help_revlogin, Line 11
    Invalid object name ‘master..sysxlogins’.
    i got this error message when i execute this message.

    Reply
    • It should be master..syslogins

      Reply
    • Leandro R Sales
      January 8, 2013 10:29 pm

      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

      —– End Script —–
      EXEC master..sp_help_revlogin
      GO

      Reply
  • Hi pinal,

    Will help me to find out why i am getting above error.

    Reply
  • Hi PinalSir,
    can you please help me out with below error.

    Msg 207, Level 16, State 1, Procedure sp_help_revlogin, Line 13
    Invalid column name ‘srvid’.
    Msg 207, Level 16, State 1, Procedure sp_help_revlogin, Line 11
    Invalid column name ‘xstatus’.
    Msg 207, Level 16, State 1, Procedure sp_help_revlogin, Line 19
    Invalid column name ‘srvid’.
    Msg 207, Level 16, State 1, Procedure sp_help_revlogin, Line 17
    Invalid column name ‘xstatus’.
    Msg 2812, Level 16, State 62, Line 2
    Could not find stored procedure ‘master..sp_help_revlogin’.

    Reply
  • it is saying “no logins found”

    Reply
  • How I can move users from SQL 2008 Server to another SQL 2008 Server

    Reply
  • Hi it’s me, I am also visiting this website daily, this site is in fact good and the people are really sharing nice thoughts.

    Reply
  • Will this work if we change the domain names for login ? Or there will be SID issue ?

    Reply
  • Pooja,

    We did exactly that almost 2 1/2 years ago and we have yet to have an issue.

    Reply
  • it works only on table and view permission but storeprocedure not executing

    Reply
  • Stefano Colombo
    September 2, 2013 6:35 pm

    Is there a way to schedule the export of logins in a txt file ?

    Reply
  • Hi, I need to transfer a login with password from SQL 2012 to SQL 2008. The problem is that the Hash value of passwords is different in 2012 than 2008, so I cannot create it in 2008.
    Any ideas to solve it?
    Thanks, Marcelo.

    Reply
  • JeremiasBadilla
    March 18, 2015 12:18 am

    thanks !!!

    Reply
  • Reply
  • hi Pinal,
    Is there any script for logins,server roles at a time? other than sp_help_revlogin

    Reply

Leave a Reply

Menu