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

admin

Leave a Reply

Your email address will not be published.