How to solve the permission problem when moving databases between servers running SQL Server

Summary

This article describes how to map standard Logon and integrated logon to solve the permission issue when moving databases between servers running SQL Server.

More information

When you move a database from a server running SQL Server to another server running SQL Server,MasterThe Security ID (SID) andUserThe user's Sid in the database may not match. By default, SQL Server 7.0, SQL Server 2000, and SQL Server 2005 provideSp_change_users_loginSystem stored procedures map these mismatched users. However,Sp_change_users_loginStored procedures can only be used to map standard SQL Server logins, and these mappings must be executed for one user at a time. RelatedSp_change_users_loginFor more information about stored procedures, 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, you can use Sid to maintainMasterLogin andUserIng between users in the database. This ing is maintainedUserREQUIRED for the correct logon permissions in the database. If this ing is lost, a permission issue occurs during logon, including but not limited to the following:

• If the new server does not have an SQL Server logon attempt, the user may receive the following error message:

Server: MSG 18456, level 16, state 1
Login Failed for user '% ls '.

• If SQL Server logon exists on the new server,MasterSid andUserIf the SID in the database is different, you can successfully log on to SQL Server. However, when you attempt to access the database, you 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, you may receive the following error message:

The server user "% s" is not a valid user in the Database "% s. Add the user account to 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 this White Paper, visit the following Microsoft Website:

Http://msdn.microsoft.com/library/default.asp? Url =/library/en-US/dnsql7/html/sql7security. asp

(Http://msdn.microsoft.com/library/default.asp? Url =/library/en-US/dnsql7/html/sql7security. asp)

For more information about the SQL Server 2000 security model, clickArticleTo view the articles in the Microsoft Knowledge Base:

322712 (http://support.microsoft.com/kb/322712/) Microsoft SQL Server 2000 s322712 security features and best practices

To download the mapsids.exe file, use the following download link:

Mapsids.exe
Http://download.microsoft.com/download/sqlsvr2000/utility/5.0/win98me/en-us/Mapsids.exe

Http://download.microsoft.com/download/sqlsvr2000/utility/5.0/win98me/en-us/mapsids.exe)

Restrictions

• IfSysusersSome users in the table do not use the computer name or the domain name that owns the object as the prefix, and the ApplicationProgramUse the name that contains two partsUsername.ObjectnameIf these objects are referenced, the application may be interrupted because whenSp_sidmapStored Procedure inSysxloginsWhen a table appears, it renames these users with the computer name or domain name as the prefix. To solve this problem, goSp_sidmapAfter the stored procedure is completeSysusersThe affected users in the table are renamed to their original names or contact your primary support provider.

• This document does not contain aliases. You must manually manage aliases.

• If no standard SQL Server logon exists on the new SQL Server server, a logon with an empty password will be added. Therefore, you may need to change these logon passwords.

• If the user isUserCreated in the database, and the user name is different fromSysxloginsIf the name is displayed in the table, you cannot know the logon information of the user. ThereforeSp_sidmapBefore the stored procedure:

1. transfer all objects owned by the user to a temporary database.

2. Delete the user, add the user with the correct name, and then move back all objects of the user.

• If a user does not have a corresponding logon and is not prefixed with a local computer name or domain name, you will receive a message about the user. The message indicates that you must first add the user at the windows level and then add the user as a login to SQL Server. Then, you must execute the command again.Sp_sidmapStored procedure.

• If you use a domain name or a local Windows server name as the prefixSysxloginsThere is no corresponding logon In the table. This stored procedure will try to add it as a new logon to SQL Server. If the Windows user does not exist, an output message is generated in the result window, and the user is created manually after the user is added for the first time.

• IfSysusersA user in the table has multiple logins. You will see an output message in the result file, which lists all logins with the same user name. In this case, you must manually intervene to ensure that the user only corresponds to one logon.
Example: IfSysusersThere is a user named "johndoe" in the table, andSysxloginsIf the table has logon names such as "test \ johndoe" and "Test2 \ johndoe", you will receive a message stating that one user has multiple logins when running the stored procedure, the system administrator must select one of them. Only in this case can you run the second stored procedure provided in this articleSp_prefix_sysusersname. In addition, this situation is also detailed in the readme.txt file.

Ing standard Logon and integrated Logon

After you move the database from one server running SQL Server to another server running SQL Server, follow these steps to minimize user intervention:

1. Ensure thatSysusersEvery user in the table,MasterDatabaseSysxloginsThere is a logon in the table.
Note:: To add a standard SQL Server logon, see the "sp_addlogin" topic in SQL Server books online. To add an integrated SQL Server logon, see the "sp_grantlogin" topic in SQL Server books online.

2. Download The mapsids.exe file and decompress the sp_sidmap. SQL and readme.txt files.

3. log on to the server running SQL server as a system administrator, and then run the sp_sidmap. SQL file in the user database. Run the sp_sidmap. SQL file to create two stored procedures:Sp_sidmapAndSp_prefix_sysusersname.

4. Ensure that no user except the user who runs these stored procedures accesses the database.

5. Make sure that the query analyzer window displays the results in text format rather than grid format. To do this, press Ctrl ^ t or clickQueryAnd then click "text display result ". This is very important. It allows you to view results and information messages in a window and save the output to a text file. Later, you may need to use this file to parse some mappings.

6. Because you cannot verify that the parameter has been correctly passed, ensure that the parameter is passedSp_sidmapStored 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 of the new and old domain names and server names as appropriate.

7. Save the results in a file and follow the instructions provided in the readme.txt file.
Note:: When you run these stored procedures, the only change table in the database isSysusersTable. If you need to return the status at the beginning, restore the database from the backup or re-attach the database.

Reference

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

274188 (http://support.microsoft.com/kb/274188/) PRB: the "isolated user troubleshooting" topic in books online is incomplete

246133 (http://support.microsoft.com/kb/246133/) how to transfer login and password between SQL Server instances

168001 (http://support.microsoft.com/kb/168001/) The user logon and permissions on the database may be incorrect after the database is restored

298897 (http://support.microsoft.com/kb/298897/) Example: mapsids.exe helps to map Sid between the user database and the master database when moving the database

From: http://support.microsoft.com/kb/240872/