Home page
 
 Home 
 ASP 
 PHP 
 SQL 
 HTML 
 JavaScript 
 Search 
 Contact 
 
Search
or browse popular tags
Subscription

Sign up for the free email newsletter for new tips, tutorials and more. Enter your email address below, and then click the button.

Privacy Policy

RSS Twitter

How to transfer logins and passwords between instances of SQL Server

Print

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.



Tags:

Add To: Add to dzone dzone | Digg this digg | Add to del.icio.us del.icio.us | Stumble it stumbleupon

  • Comments





Copyright © 2005-2014             www.WebCheatSheet.com All Rights Reserved.