Transfer logins and passwords between instances of SQL Server

This article describes how to transfer the logins and the passwords between different instances of SQL Server running on Windows.

Original product version:   SQL Server
Original KB number:   918992

Introduction

This article describes how to transfer the logins and passwords between different instances of Microsoft SQL Server.

 Note

The instances may be on the same server or on different servers, and their versions may differ.

More information

In this article, server A and server B are different servers.

After you move a database from the instance of SQL Server on server A to the instance of SQL Server on server B, users may be unable to log in to the database on server B. Additionally, users may receive the following error message:

Login failed for user 'MyUser'. (Microsoft SQL Server, Error: 18456)

This problem occurs because you did not transfer the logins and the passwords from the instance of SQL Server on server A to the instance of SQL Server on server B.

 Note

The 18456-error message also occurs due to other reasons. For additional information on these causes and potential resolutions review MSSQLSERVER_18456.

To transfer the logins, use one of the following methods, as appropriate for your situation.

• Method 1: Reset the password on the destination SQL Server computer (Server B)

  To resolve this issue, reset the password in SQL Server computer, and then script out the login.

   Note

  The password hashing algorithm is used when you reset the password.

• Method 2: Transfer logins and passwords to destination server (Server B) using scripts generated on source server (Server A)

  1. Create stored procedures that will help generate necessary scripts to transfer logins and their passwords. To do this, connect to Server A using SQL Server Management Studio (SSMS) or any other client tool and run the following script:

     SQL

     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 procedures are named sp_hexadecimal and sp_help_revlogin .

  2. In the SSMS query editor, select the Results to Text option.

  3. Run the following statement in the same or a new query window:

     SQL

     EXEC sp_help_revlogin

  4. The output script that the sp_help_revlogin stored procedure generates is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.