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

, , ,
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

  • the sql script page link is not working. Plz check.

    Reply
  • Right click on and select Save As…
    It will not work when you just left click on it.

    Reply
    • 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.

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

    Reply
  • 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

    Reply
  • As few readers complained about downloading script error. I have put the code of the script in the post itself.

    Hope this will help.

    Kind Regards,
    Pinal

    Reply
  • 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??

    Reply
  • This does not work in 2005 as the system tables ‘master..sysxlogins’. has changed. Review!

    Reply
  • Hey Pinal Dave,

    Will this work when trying to migrate logins from SQL 2005 to 2008?

    Reply
  • 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?

    Reply
  • 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

    Reply
  • Doesn’t work. no such table as sysxlogins on my SQL Server 2005 SP3

    Reply
  • 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

    Reply
  • Above one for MSSQL2008

    Reply
    • 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

      Reply
  • 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?

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

      Reply
  • Rahul Trehan
    March 3, 2011 6:38 pm

    Hi raji thanks you but i am doing that and having sql server 2005 but thats not solving the problem

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • Do know a way to do a similar transfer with credentials?

    Reply
  • excellent

    Reply
  • 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

    Reply

Leave a Reply