Migrating Logins between Servers

Before Logins can be generated, 2 procedures have to be added to the master database. These are Microsoft built but come unsupported.

===================================================================


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

===================================================================

To Extract logins just run

sp_help_revlogin

on the master database on the server. This extracts logins with encrypted passwords

Check http://support.microsoft.com/kb/246133 for full support details

Advertisements

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

%d bloggers like this: