How to transfer logins and passwords between instances of SQL Server

To transfer logins and passwords between different versions of SQL Server, follow these steps:

1. Run the following script on the source SQL Server. Continue to step 2 when you finish creating the sp_help_revlogin stored procedure.

   ----- Begin Script, Create sp_help_revlogin procedure -----
   
   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 -----
   

2. After you create the sp_help_revlogin stored procedure, run the sp_help_revlogin procedure from Query Analyzer on the source server. The sp_help_revlogin stored procedure can be used on all versions of SQL Server. The output of the sp_help_revlogin stored procedure is login scripts that create logins with the original SID and password.

   EXEC master..sp_help_revlogin
   

   Sample output:

   DECLARE @pwd sysname
   
   -- Login: 71EDC628A9574AB7BEE1EA914F1A0A7E
   SET @pwd = CONVERT (varbinary(256), 0x01003069FE2D1BFBF6407872FA61BE9C669B632F05C458B2B98B083AAA835EF208D28C84CFB753008C50D42C759D)
   EXEC master..sp_addlogin '71EDC628A9574AB7BEE1EA914F1A0A7E', @pwd, @sid = 0x2E8DF4909C0CEB4B8C8A49493414B759, @encryptopt = 'skip_encryption'
    
   -- Login: admin
   SET @pwd = CONVERT (varbinary(256), 0x0100CA7C6475AC2FF8DC9247A785BFDC4EC0EA3863820ACD2E2B0EF24A434708AA91793114CE0F4939EC013053E5)
   EXEC master..sp_addlogin 'admin', @pwd, @sid = 0x9FDF121BED431D4EB7EAC551F67D276A, @encryptopt = 'skip_encryption'
   
   ...
   

3. Save the output, and then paste and run it in SQL Server Management Studio on the destination SQL Server.