SQL DBA Guru

Case Scenario

I am a DBA and upgrading a SQL Server from SQL Server 2005 to SQL Server 2008. Though I have upgraded the databases but there are hundreds of SQL Login IDs which too needed to be migrated from 2005 box to 2008 box. How should I transfer the logins and passwords from one instance of SQL Server 2005 to another instance of SQL Server 2005 or SQL Server 2008?

Resolution

Sometimes, the DBA is requested to migrate or upgrade a database from one SQL Server to another.  And one of the tasks is to move the logins and passwords to the destination server as well. So, this article is going to guide us what to do in this case scenario.

After you move a database from the instance of SQL Server on server A to the instance of SQL Server on server B, the users may not be able to log in to the database on server B because we haven’t transferred the logins and the passwords yet. To transfer the logins and the passwords from the instance of SQL Server on server A to the instance of SQL Server on server B, follow these steps:

1. On server A, start SQL Server Management Studio, and then connect to the instance of SQL Server from which you moved the database.
2. Open a new Query Editor window, and then run the following script.

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

Note This script creates two stored procedures in the master database. The two stored procedures are named the sp_hexadecimal stored procedure and the sp_help_revlogin stored procedure.

1. Run the following statement.

EXEC sp_help_revlogin

1. The output script that is generated by the sp_help_revlogin stored procedure is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.
2. On server B, start SQL Server Management Studio, and then connect to the instance of SQL Server to which you moved the database.
   
   Important Before you go to step 5, review the information in the "Remarks" section.
3. Open a new Query Editor window, and then run the output script that is generated in step 3.

Remarks:

Review the following information before you run the output script on the instance on server B

• You need to change the login IDs if the server A and B are in different domains.
• By default, only a member of the sysadmin fixed server role can run a SELECT statement from the sys.server_principals view.
• In the output script, the logins are created by using the encrypted password.