Article ID: 240872
Article Last Modified on 11/26/2007
APPLIES TO
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 2000 64-bit Edition
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft SQL Server 2005 Developer Edition
- Microsoft SQL Server 2005 Enterprise Edition
- Microsoft SQL Server 2005 Express Edition
- Microsoft SQL Server 2005 Standard Edition
- Microsoft SQL Server 2005 Workgroup Edition
This article was previously published under Q240872
SUMMARY
This article describes how to map the standard and integrated logins in order to resolve permission issues when you move a database between servers that are running SQL Server.
MORE INFORMATION
When you move a database from one server that is running SQL Server to another server that is running SQL Server, a mismatch may occur between the security identification numbers (SIDs) of the logins in the master database and the users in the user database. By default, SQL Server 7.0, SQL Server 2000, and SQL Server 2005 provide the sp_change_users_login system stored procedure to map these mismatched users. However, you can only use the sp_change_users_login stored procedure to map standard SQL Server logins and you must perform these mapping for one user at a time. For more information about the sp_change_users_login stored procedure, see the "sp_change_users_login" topic in SQL Server 7.0,SQL Server 2000, and SQL Server 2005 Books Online.
In SQL Server 7.0 or later versions, you can maintain the mapping between the logins in the master database and the users in the user database by using the SIDs. This mapping is required to maintain correct permissions for the logins in the user databases. When this mapping is lost, the logins have permission issues that include but are not limited to the following:
- If the SQL Server login does not exist on the new server, and the user tries to log on, the user may receive the following error message:
- If the SQL Server login exists on the new server, but the SID in the master database differs from the SID in the user database, the user can log on to SQL Server successfully; however, when the user tries to access that database, the user may receive the following error message:
Note In SQL Server 2005, the user may receive the following error message:
For more information about the SQL Server 7.0 Security model, see the "Microsoft SQL Server 7.0 Security" white paper. To view the white paper, visit the following Microsoft Web site:
For more information about the SQL Server 2000 Security model, click the following article number to view the article in the Microsoft Knowledge Base:
322712 Microsoft SQL Server 2000 S322712 Security Features and Best Practices
Restrictions
- If there are users in the sysusers table without a prefix of the computer name or the domain name that own objects, and these objects are referenced in applications by using the two-part name
username.objectname, the application may break because the sp_sidmap stored procedure renames these users with the prefix of the computer name or domain name as it appears in the sysxlogins table. To work around this problem, after the sp_sidmap stored procedure is completed, rename the users who were affected in the sysusers table to their former names or contact your primary support provider. - This article does not consider aliases. You must manage the aliases manually.
- If a standard SQL Server login does not exist on the new SQL Server server, you can add the login with a NULL password. You may have to change the password for these logins accordingly.
- If a user was created in the user database with a name that differs from that which appears in the sysxlogins table, it is impossible to know the corresponding login for that user. Therefore, before you run the sp_sidmap stored procedure:
- Transfer all the objects that this user owns to a staging database.
- Drop the user, add the user that has the correct name, and then transfer back all the objects for this user.
- If a user has neither a corresponding login nor a prefix of either the local computer name or the domain name, you receive a message for this user. This message indicates that you must first add the user at the Windows level and then add it to the SQL Server as a login. After you do this, you must run the sp_sidmap stored procedure again.
- If a user has a prefix of either the domain name or the local Windows server name, but the corresponding login does not exist in the sysxlogins table, the stored procedure tries to add this as a new login to SQL Server. If the Windows user does not exist, it generates an output message in the results window and then manually creates the login after it first adds the Windows user.
- If there is more than one login for a user in the sysusers table, you see an output message in the results file and it lists all the logins that have the same username. At this point, you must manually intervene to make sure that the user corresponds to only one login.
Example If the sysusers table has a user named "johndoe" and the sysxlogins table has logins with names such as "Test\johndoe" and "Test2\johndoe", when you run the stored procedure, you receive a message that states that one of the users has more than one login and that the System Administrator must choose one. This is the only time that you must run the second stored procedure, sp_prefix_sysusersname, which is provided in this article. Additionally, this situation is described in detail in the Readme.txt file.
Map the standard and integrated logins
After you move a database from one server that is running SQL Server server to another server that is running SQL Server server, follow these steps for minimal user intervention:
- Make sure that there is a login in the sysxlogins table in the master database for each user in the sysusers table of the database.
Note To add a standard SQL Server login, see the "sp_addlogin" topic in SQL Server Books Online. To add an integrated SQL Server login, see the "sp_grantlogin" topic in SQL Server Books Online. - Download the MapSids.exe file, and then extract the Sp_sidmap.sql and Readme.txt files.
- Log on to the server that is running SQL Server as a system administrator, and then run the Sp_sidmap.sql file in the user database. Running the Sp_sidmap.sql file creates the two stored procedures, sp_sidmap and sp_prefix_sysusersname.
- Make sure that the database is not accessed by any other user than the one who is running the stored procedures.
- Make sure that Query Analyzer displays results in text format and not in grid format. To do this, either press the CTRL^T keys, or click Query, and then click Results in Text. This is very important so that you can view the results and the informational messages in one window and save the output to a text file. You might need this file later to resolve some of the mappings.
Because you cannot verify whether the parameters are passed correctly, make sure to pass them correctly to the sp_sidmap stored procedure:
EXEC sp_SidMap @old_domain = old_domain_name, @new_domain = new_domain_name, @old_server = old_server_name, @new_server = new_server_name
Replace the values for the old and new domain names and server names appropriately.
- Save the results in a file and follow the directions that are provided in the Readme.txt file.
Note When you run these stored procedures, the sysusers table is the only table that changes in the database. To return to a state where you started, restore the database from the backup or reattach the database.
REFERENCES
For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
274188 "Troubleshooting orphaned users" topic in Books Online is incomplete
246133 How to transfer logins and passwords between instances of SQL Server
168001 User logon and/or permission errors after restoring dump
298897 SAMPLE: Mapsids.exe Helps Map SIDs Between User and Master Databases When Database Is Moved
Keywords: kbhowtomaster KB240872
