Transfer logins and passwords between the instances of SQL Server 2005 and 2008

At times we need to replicate the same database from one server to another, after moving users may not be able to use the database and receive the error as

Login failed for user ‘TestUser’, (Microsoft SQL Server, Error: 18456)

This may be due to unavailable of logins and passwords on the instance to which the database has been moved. To transfer the logins and passwords from one instance to another follow the below steps,

·        Connect to the instance from where the database has moved.

·        Copy the below scripts into the query window and execute

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 authentications

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

·        The above script creates two stored procedures in the master database, they are sp_hexadecimal and the sp_help_revlogin stored procedures.

·         Once the stored procedure gets created, execute it in a new query editor window.

EXEC sp_help_revlogin

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

·        Start SQL Server Management Studio and then connect to the instance of SQL Server to which the database has moved

·        Open a new Query Editor window, and then run the output script that is generated. you can edit the script to avoid creating the redundant logins. Before run the script to the destination server read the below points.

Note:

·        Assess the output script cautiously. If server 1 and server 2 are in different domains, you will have to modify the output script and replace the original domain name with the new domain name in the CREATE LOGIN statements. The integrated logins that are granted access in the new domain do not have the same SID as the logins in the original domain. Therefore, the users are orphaned from these logins. To resolve the permission problem, refer this link, http://support.microsoft.com/kb/240872

·        The script does not transfer the default database information for a particular login, because the default database may not always exist on server 2. To define the default database for a login, use the ALTER LOGIN statement by passing in the login name and the default database as arguments.

·        The sort order of both the servers may be case sensitive, or the sort order of both the servers may be case insensitive. In these cases, the users do not experience a problem. If not, users will experience a problem.