How to: Transfer logins and passwords between instances of SQL Server

Server overview After you move a database to a new server, users may not be able to log on to the new server. Instead, they receive the following error message:
MSG 18456, level, State 1
Login failed for user '%ls '.

You must transfer the login and password to the new server. This article describes how to transfer logins and passwords to a new server.

Back to the top of the page
How to transfer logins and passwords between servers running SQL Server 7.0 SQL Server 7.0 Data Transformation Services (DTS) object transfer features the ability to transfer logins and users between two servers, but it does not transfer the password for SQL Server Authentication logins. To transfer logins and passwords from one server running SQL Server 7.0 to another server running SQL Server 7.0, follow the instructions in the "Creating and running stored procedures in the Master database" section of this article. You will create the sp_help_revlogin stored procedure on the source server. This process generates a script that you can run on the target server to re-create the login with the original security identification number (SID) and keep the current password.

Back to the top of the page

How to transfer logins and passwords from SQL Server 7.0 to SQL Server 2000 or to a server running SQL Server 2000 from a SQL Server 7.0 server to an instance of SQL Server 2000 or in S QL transfer Logins and passwords between two instances of Server 2000, you can use the new DTS Package Transfer Logins Task (DTS Package Transfer logon Task) in SQL Server 2000. To use this task, follow these steps:

1. Connect to the SQL Server 2000 destination server, move to Data Transformation Services in SQL Server Enterprise Manager, expand this folder, right-click the local package, and then click the new package. 2. After you open the DTS package Designer, click the Transfer Logon task on the Task menu. Complete information about the source, destination, and Logon tabs as needed.

IMPORTANT: The SQL Server 2000 target server cannot run a 64-bit version of SQL Server 2000. The DTS component for the 64-bit version of SQL Server 2000 is not available. If you want to import logins from an instance of SQL Server on another computer, your SQL Server instance must be running under a domain account to complete this task.

Note: You can use the DTS method or the script in the "Create and run stored procedures in Master Database" section of this article to transfer logins from SQL Server 7.0 to SQL Server 2000 or to instances of SQL Server 2000. The DTS method transmits the password, but does not transmit the original SID. If the login was not created with the original SID and the user database is also transferred to a new server, the database user will be orphaned from that login. To transfer the original SID and bypass orphaned users, use the script in the next section of this article instead of the DTS method. Back to the top of the page

To create and run stored procedures in the Master database, review the comments at the end of this article for important information about the following steps.
1. Run the following script on the source SQL Server. This script creates two stored procedures with the name Sp_hexadecimal and Sp_help_revlogin, respectively, in the master database. Proceed to step 2nd after you have created the procedure.

Note: The following procedure depends on the SQL Server system table. The structure of these tables may change between different versions of SQL Server, and do not select directly from the system tables.

-----Begin Script, Create sp_help_revlogin procedure-----Use mastergoif object_id (' sp_hexadecimal ') is not NULL DROP PR Ocedure sp_hexadecimalgocreate PROCEDURE sp_hexadecimal @binvalue varbinary (256), @hexvalue varchar (256) Outputasdeclare @charvalue varchar (256) DECLARE @i intdeclare @length intdeclare @hexstring char () SELECT @charvalue = ' 0 X ' SELECT @i = 1SELECT @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 + SUB STRING (@hexstring, @firstint +1, 1) + SUBSTRING (@hexstring, @secondint +1, 1) SELECT @i = @i + 1ENDSELECT @hexvalue = @charv Aluegoif object_id (' sp_help_revlogin ') is not NULL DROP PROCEDURE sp_help_revlogingocreate PROCEDURE sp_help_revlogin @l Ogin_name sysname = NULL asdeclare @name sySnamedeclare @xstatus intdeclare @binpwd varbinary (256) DECLARE @txtpwd sysnamedeclare @tmpstr varchar (256) DECLARE @SID_ varbinary varbinary 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 are NULL and name <> ' sa ' ELSE DECLARE login_curs CURSOR for SELECT SID, Name, Xstatus, Passwor D from Master. sysxlogins WHERE Srvid is NULL and name = @login_nameOPEN Login_cursfetch NEXT from login_curs into @SID_varbinary, @name, @xstatus, @binpwdIF (@ @fetch_status =-1) BEGIN PRINT ' No login (s) found. ' Close login_curs deallocate login_curs return-1endset @tmpstr = '/* sp_help_revlogin script ' PRINT @tmpstrSET @tmpstr = ' * * generated ' + CONVERT (varchar, GETDATE ()) + ' on ' + @ @SERVERNAME + '/' Print @tmpstrPRINT ' print ' DECLARE @pwd sysn Ame ' while (@ @fetch_status <>-1) Begin IF (@ @fetch_status <>-2) Begin PRINT ' SET @tmpstr = '--Login: ' + @n Ame PRINT @tmpstr IF (@xstatus & 4) = 4 Begin--NT authenticated Account/group IF (@xstatus & 1) = 1 Begin--NT login is denied ACCE SS 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 ELSE BEGIN--SQL Server authentication IF (@binpwd isn't NULL) be GIN-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 @tmpstrEXEC sp_hexadecimal @SID_varbinary, @SID_string out SET @tmpstr = ' EXEC master. Sp_addlogin ' + @name + ', @pwd, @sid = ' + @SID_string + ', @encryptopt = ' End ELSE BEGIN--Null passwordexec sp_he Xadecimal @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 FETCH NEXT from login_curs into @SID_varbinary, @name, @xstatus, @bi Npwd endclose login_cursdeallocate login_cursreturn 0GO-----End Script-----
2. After you create the sp_help_revlogin stored procedure, run the Sp_help_revlogin procedure from the Query Analyzer on the source server. Sp_help_revlogin stored procedures can be used for both SQL Server 7.0 and SQL Server 2000. The output of the sp_help_revlogin stored procedure is a logon script that creates a login with the original SID and password. Save the output and paste it into the Query Analyzer on the destination SQL Server and run it. For example:
EXEC Master.. Sp_help_revlogin
Back to the top of the page

Note • Carefully review this script before running the output script on the destination SQL Server. If you must transfer the login to an instance of SQL Server that is not in the same domain as the SQL Server source instance, edit the script generated by the Sp_help_revlogin procedure and replace the domain name with the new domain name in the sp_grantlogin statement. Because an integrated login that is granted access in a new domain has a different SID than the logins in the original domain, database users will be orphaned from these logins. To resolve these orphaned users, see the articles that are referenced in the following bullet items. If you transport an integrated login between instances of SQL Server in the same domain, the same SID is used, and users are less likely to be orphaned. • After you move the login, the user no longer has access to the database that has been moved concurrently. This problem is known as orphaned users. If you try to grant this login permission to access this database, you may fail, indicating that the user already exists:
Microsoft SQL-DMO (ODBC sqlstate:42000) Error 15023:user or role '%s ' already exists in the current database. about how to map logins to databases User to resolve orphaned SQL Server logins and integrated logins, see the following Microsoft Knowledge Base article: 240872 How to: Resolve Permissions issues When moving a database between SQL servers
For instructions on resolving orphaned users one by one using sp_change_users_login stored procedures, which can only resolve isolated users from standard SQL logins, see the following article in the Microsoft Knowledge Base: 274188 PRB: "Troubleshooting Orphaned Users Topic in the books Online is incomplete if the transfer of logins and passwords is part of moving a database to a new server running SQL Server, see the following article in the Microsoft Knowledge Base to learn about Description of workflow and steps: 314546 how To:move Databases Between Computers that Are Running SQL server can do this because of the sp_addlogin system stored procedure @encryptopt parameter allows you to create a login by using an encrypted password. For more information about this procedure, see the "sp_addlogin (T-SQL)" topic in SQL Server Books Online. • By default, only members of the Sysadminfixed server role can select from the sysxlogins table. End users will not be able to create or run these stored procedures unless they are granted the necessary permissions by members of the sysadmin role. • This method does not attempt to transfer the default database information for a particular login because the default database does not always exist on the target server. To define a default database for a login, you can use the sp_defaultdb system stored procedure and pass the login name and default database as parameters to the procedure. For more information about using this procedure, see the "sp_defaultdb" topic in SQL Server Books Online. • During the transfer of logins between instances of SQL Server, if the sort order of the source server is case-insensitive and the sort order of the target server is case-sensitive, then all alphabetic characters must be entered in uppercase in the password after the login is transferred to the destination server. If the sort order of the source server is case sensitive and the sort order of the destination server is case-insensitive, you cannot log on by using the login that is transmitted with the procedure outlined in this article, unless the original password contains no alphabetic characters, or all alphabetic characters in the original password are uppercase. This problem does not occur if all two servers are case-sensitive or case-insensitive. This is a side effect of how SQL Server handles passwords. For more information, see "Effect o" in SQL Server 7.0 online Booksn Passwords of changing sort orders (changes the sort order effect on passwords) topics. • When you run the output of the Sp_help_revlogin script on the server, if the server has defined a login that has the same name as a login in the script output, you may see the following error message when you perform the output of the Sp_help_revlogin script:


Server:msg 15025, level, State 1, Procedure sp_addlogin, line 56
The login ' test1 ' already exists.

Similarly, if there are other logins on this server with the same SID value as the login you are trying to add, you receive the following error message:


Server:msg 15433, level, State 1, Procedure sp_addlogin, line 93
Supplied parameter @sid is in use.

Therefore, you must carefully review the output of these commands, check the contents of the sysxlogins table, and resolve the errors accordingly. • The SID value for a specific login is used as the basis for implementing database-level access in SQL Server. Therefore, if the same login has two different SID values at that database level (in two different databases on that server), this login will only have access to the database whose SID matches the value in the syslogins of the login. This can happen if the two databases being discussed have been merged from two different servers. To resolve this issue, use the Sp_dropuser stored procedure to manually delete the discussion login from a database with a mismatched SID, and then add it using the sp_adduser stored procedure.