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 (http://blog.SQLAuthority.com) , kb 918992

About these ads

38 thoughts on “SQL SERVER – Transfer The Logins and The Passwords Between Instances of SQL Server 2005

    • Hi Sir,

      I need the best practices how to move the existing MS SQL SERVER 2008 MGMt Studio, to the SQL server 2008 Eneterprise Edition.

      My existing DB is publisher DB, and they should not get disturbed and i am expecting very low Downtime of the server.

      And my New server is having 36Processors 64bit and 258GB RAM i need to use all the resources in the system,

      Please suggest ASAP.

      Thanks alot.

  1. Dude,
    Right click and download works but not just clicking on it as browser does not open .sql file. Good work Dave

  2. Hello Pinal Dave,
    Thank you for your website and blogs online that helps me in great deal, whenever I need to do errors reseach or learn a new features of SQL Server 2005…your site is great and a powerful site I visit it alot….I also had used your SQL Server books for years. Thank You!

    I just found in this web page the download Script link is not avaliable. Please check it out for me… Thank You.

    I will briefly describe the solution here : Download the SQL Script. (Right Click and Save As…)

    DBA
    Denise

  3. This script is good if the destination server does not have logins. But once we have Prod and DR servers setup with logins synched using these procs, how do we then update passwords between Prod and DR whenever the pwd changes in Prod for SQL logins??

  4. How do yo transfer logins & passwords from sql 2005 to sql 2000.
    Can get it to work going forward but not backwards. Is this even possible?

  5. Hi Pinal,

    I have 1 small requirement. in production server i have around 30 logins , 50 jobs , replications and linked servers .
    I want generate scripts for all logins,jobs, replication,and linkedservers how can i generator. is there any script for generation please help me pinal.

    Thanks in advance.

    –Satheesh

  6. 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

    EXEC sp_help_revlogin

    • HI Mittal,

      we use mixed authentication.

      will it work for windows logins+sql logins both or only windows logins?
      if so how do I copy SQL logins?

      Pls help

  7. Hi Pinal,
    I am having problem that when my sql performance seems sluggish i have to restart sql server.Is there any way to free memory & resources without restarting sql server?

    • ya it possible but u have to shrink the tempdb and log files,
      and also while taking backup take compressed backup.
      i will helpfull

  8. Guys,

    There is another problem I found out that after running the scirpt the system is not accessible it says users does not exists what I found that it is looking now in SQL server database users name as case senstive whereas both sql 2000 (source db) and sql 2005 (target) db are configure using the same collation and locale.

    But when you run the script this problem is arising like If I wanted to access the user I have to give UPPER CASE. because sql 2005 now using case sensitive users name. Password field is okay.

  9. Hello Pinal Dave,
    Thanks for your post. I want to migrate my “Application role” with password between two sql server 2008 instances. Can you please help me on this, Thanks.

  10. Hi Pinal,

    I have sync the logins from Server A to Server B on every weekend
    eg: in Server A we have 20 users & Logins after 7 days it will added 2 new users
    i want to transer the this new users or logins with PW i m trying find the script for this but no luck do you any suggestions on this really help ful to me.
    Tx

  11. 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.

    • 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

  12. 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’.

  13. Pingback: SQL SERVER – Weekly Series – Memory Lane – #018 | SQL Server Journey with SQL Authority

  14. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s