How to resolve permission issues when you move a database between servers that are running SQL Server (240872)
The information in this article applies to:
- Microsoft SQL Server 2000 (all editions)
- Microsoft SQL Server 2000 64 bit (all editions)
- Microsoft SQL Server 7.0
- 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
This article was previously published under Q240872 SUMMARYThis 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:
Server: Msg 18456, Level 16, State 1 Login
failed for user '%ls'. - 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:
Server: Msg 916, Level 14, State
1, Line1 Server user '%.*ls' is not a valid user in database '%.*ls'. Note In SQL Server 2005, the user may receive the
following error message: Server user '%s' is not a valid user in database '%s'. Add the user account into the database first.
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
To download the Mapsids.exe file, use the following download link: 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
Modification Type: | Major | Last Reviewed: | 12/13/2005 |
---|
Keywords: | kbHOWTOmaster KB240872 kbAudDeveloper kbAudITPRO |
---|
|