Restoring your database is one of the most important tasks you might ever have to perform. You plan your recovery operation knowing that if a disaster strikes, your organization's future might depend on your ability to make your data available quickly. In "All About Restore," May 2002, I discussed how to restore a database on top of an old version or to a new location on the same SQL Server. But in some situations, you might want to restore a database backup onto an entirely new server. You might simply want to create a separate SQL Server installation for testing, training, or debugging purposes. You can also restore a SQL Server 7.0 database to a separate SQL Server 2000 server to perform an upgrade of a single database from SQL Server 7.0 to 2000. But in a disaster situation in which your entire system is damaged, you might need access to the data as quickly as possible.
When you move a backup of a SQL Server database from one server to another, you encounter some specific challenges. A common problem is that in the restore process, usernames and login names can be mismatched. Let's look at why usernames and login names are important, why mismatched names are a problem, and how to use a special procedure called sp_sidmap to avoid such problems.
Who's Who?
The most important concern you have when you restore a database backup to a new system is making sure that only the appropriate users have access to the restored database. Not only is this complex problem difficult to solve, but it's also difficult to understand. To make sure that a newly restored database allows access to the same users as the original database did, you have to clearly understand the distinction between a SQL Server login name and a database username, both of which need to be properly set up before users can access the data. One reason this distinction between login names and database usernames is difficult to understand is that even SQL Server Books Online (BOL) doesn't always clearly distinguish between these two levels of access into a SQL Server system. To understand the details, you also need to understand the difference between SQL Server authentication and Windows authentication. Authentication is frequently misunderstood but is crucial for a correct restore operation onto a new server—however, the topic is beyond the scope of this article. For information about authentication modes and planning your system security implementation, see Morris Lewis's articles "Security in SQL Server 7.0," December 1999 and "Creating a Manageable Security Plan," October 2000.
Users and the Restore Process
The distinction between login name and username is an important concept. Having a login name for SQL Server only lets you in the front door. To get into a database, you must also have a username in that database.
To access any objects in the database, your username must have appropriate rights. (For more information about the distinction between logins and usernames, see the Web-exclusive sidebar "Database Access.")
So, why is the difference between login names and database usernames important to restoring a database? When you restore a database, all data in that database, including all the system tables, is copied to the new location. This new copy includes the sysusers table, which contains the list of valid usernames in the database and SIDs that each username corresponds to. The SIDs in sysusers correspond to usernames on the original system. Typically, a SID in sysusers maps directly to a SID in the sysxlogins table in the master database of the original server, and the original sysxlogins table also contains the login name corresponding to that SID. A problem you might encounter when you restore a database to a new system is that the login names and SIDs on the original system might not be the same as the login names and SIDs already on the new system.
Some hypothetical questions might help you understand why this problem isn't easy to solve. If the original database has a row in sysusers for username joe that maps to the login name DBAdminDomain\joe_blow, what login name should the username joe map to when you move the database to a new domain? The SID that joe had on the original domain won't exist in the new domain. If the new domain already has a login name joe or joe_blow, should you assume that your database username joe should map to the login name joe in the new domain? If so, you'd automatically give the new domain's joe access to your server and your database even if he couldn't access SQL Server before. What if the restored database contains a user named joe and the new server has a login Domain1\joe and a login Domain2\joe? What if nothing in the new sysxlogins table resembles a login name in the restored sysusers table? Let's look at a solution that Microsoft provides to help you answer these kinds of questions.
Solving the Username Problem
SQL Server 2000 and 7.0 supply a stored procedure called sp_change_users_login that's intended to correct problems only for SQL Server—authenticated login names. This stored procedure's auto_fix option automatically updates the sysusers table in a restored database. When the SID of a SQL Server—authenticated username in sysusers doesn't map to a row in sysxlogins on the new server, SQL Server checks to see whether a sysxlogins row that has the same name exists. If SQL Server finds a matching name, it updates the SID in sysusers to match the SID in sysxlogins. Microsoft recommends that when you use auto_fix, you examine the output of sp_change_users_login to make sure that the mappings the procedure made are what you want.
Another option of sp_change_users_login lets you map users in a database to login names one at a time, so you can specify what login name a user should map to. (You can read about sp_change_users_login in BOL.) You can also map any username to a matching login name. Sp_change_users_login works for SQL Server authentication, but for security reasons, Microsoft recommends that you use Windows authentication. So if you've followed Microsoft's guidelines and you're using Windows authentication, how do you make sure logins and usernames map correctly in your new database? In the summer of 2001, almost a year after the release of SQL Server 2000, Microsoft provided a solution: a set of two stored procedures that analyze and change the sysusers table in a restored database. Administrators who fully understand the problems associated with login and user mappings and who have experience directly updating system tables might have developed their own similar solutions. Microsoft provided a 90-percent-complete solution in the updated article "INF: How to Resolve Permission Issues When a Database is Moved Between SQL Servers" at http://support.microsoft.com/default.aspx?scid=kb;en-us;q240872 and the article "SAMPLE: Mapsids.exe Helps Map SIDs Between User and Master Databases When Database is Moved" at http://support.microsoft.com/default.aspx?scid=kb;en-us;q298897. Because the solution isn't complete, you still need to understand the relationship between users and logins. The article "INF: How to Resolve Permission Issues When a Database is Moved Between SQL Servers" describes the uses and limitations of the two new stored procedures: sp_sidmap and sp_prefix_sysusersname. The article "SAMPLE: Mapsids.exe Helps Map SIDs Between User and Master Databases When Database is Moved" directs you to download a self-extracting .zip file that contains the source code for these two procedures and a readme file.
Because Microsoft provides a lot of documentation in the articles and in the readme file, I won't explain in detail how to use these procedures. But I will describe the most important features and a few limitations. The most important of the two procedures is sp_sidmap; it can replace the supplied stored procedure sp_change_users_login I described above. Sp_sidmap creates new SQL Server—authenticated login names to match any orphan SQL Server—authenticated names in the restored database. In most cases, sp_sidmap also creates new Windows-authenticated login names to match orphan Windows-authenticated names in the restored database. The procedure can distinguish between users who are mapped to SQL Server—authenticated names and users who are mapped to Windows-authenticated names by looking at a column in the sysusers table called isntname. If the isntname column has a value of 1, the user in that row is associated with a Windows-authenticated login name; if isntname has a value of 0, the row in sysusers refers to a database role, to a special user such as guest, or to a SQL Server—authenticated login.
You use the secondary procedure sp_prefix_sysusersname only when a database username could be mapped to two possible Windows-authenticated login names in two different domains or machines. When you run sp_sidmap, the procedure will report when you've encountered such a situation and will tell you which users and which probable login matches exist. You can then run sp_prefix_sysusersname to specify the prefix you want on the login name to associate with the database user.
When you run sp_sidmap, you must be careful to correctly specify the four required parameters: the domain name where the original database existed, the domain name where the database has been restored to, the machine name where the original database existed, and the machine name where the database has been restored to. Most of sp_sidmap's work involves searching the sysusers table for the original domain or machine names and replacing them with the new names. If a user already has a corresponding login name in the sysxlogins table on the new server, the procedure updates the sysusers table with the corresponding SID value. If the user doesn't already have a corresponding login name but an appropriate corresponding user already exists on the new local machine or new domain, the sp_sidmap procedure will create a new Windows-authenticated login. For example, if the restored database has a user named OldDomain\Mary and the new server has a login name NewDomain\Mary, sp_sidmap will update the row in sysusers for OldDomain\Mary to NewDomain\Mary and will update the SID in the sysusers row to match the SID in sysxlogins. If NewDomain\Mary didn't exist in sysxlogins but the NewDomain contained a valid OS user Mary, sp_sidmap would create a new login NewDomain\Mary and make the same changes to the sysusers table. A problem would occur only if NewDomain contained no appropriate username. In that case, sp_sidmap would report an error.
An undocumented behavior of sp_sidmap is that as long as an appropriate login exists in sysxlogins, the procedure doesn't confirm whether the user is still valid either in the domain or on the local machine. If a domain administrator had previously dropped NewDomain\Mary as a domain user, the login name wouldn't automatically be dropped from SQL Server's sysxlogins table. And because the name exists in sysxlogins, the sp_sidmap procedure will use the name and its now-meaningless SID to update the restored sysusers table. If the new SQL Server is configured to run in Windows-authentication mode only, the original Mary from the old domain won't be able to log in to the restored database, and sp_sidmap won't warn you that she no longer has access.
The article "INF: How to Resolve Permission Issues When a Database is Moved Between SQL Servers" strongly recommends that before you run sp_sidmap, you make sure that all the appropriate users exist in the domain or on the local machine and that they've all been granted access to SQL Server so that they have rows in sysxlogins. Although I agree with that advice, if you really want to have a username that's unrelated to the login name, sp_sidmap alone can't fix the situation. For example, if your original server contains the login name OldDomain\charlie, which has the corresponding username carlos, you have two possible solutions. Microsoft's solution is for the DBA to manually update the sysusers table after running sp_sidmap; however, you shouldn't try this solution unless you're comfortable with system table updates. Microsoft recommends that before you run the sp_sidmap procedure, you transfer to a staging database all the objects that charlie owns, drop the user carlos, add the user charlie, then transfer back all the objects that previously belonged to carlos.
This solution seems overly complicated, especially because Microsoft provides a stored procedure called sp_changeobjectowner. Instead of using Microsoft's solution, you could add a new user charlie to the database and use sp_changeobjectowner to change the owner of all carlos's objects to charlie. Then you could drop the user carlos before backing up the database. Of course, neither Microsoft's suggestion nor mine covers the rare situation in which you want to retain the user carlos, even though the login name is charlie. If you really do want such a mismatch of names, you have to directly update the system tables after running sp_sidmap to take care of all users that can be connected to an existing login name.
Handling dbo
The documentation for sp_sidmap doesn't mention any special handling for the username dbo. If your restored database has an owner that's a Windows-authenticated login, you'll get an error message returned for dbo when you run sp_sidmap because the procedure looks for a login name that matches the username, and the login name dbo doesn't exist. The best solution to this problem is to make sure the sa login is the owner of any database that you're moving to a new system. Thus, the owner is a SQL Server—authenticated login, which is much easier to deal with after you move the database. Because the new server will have a login with the name sa and the same SID as the login on the original server, sp_sidmap will have no problems establishing which login should map to the username dbo.
The Usual Warnings
Before you use the sp_sidmap procedure on a production database, test it. Sp_sidmap can encounter many possible situations that I haven't mentioned here, but they're listed in the readme file that's available with the procedure's source code. Make sure you understand exactly what each error means so that you'll know what the fixed value should be and how to correct the error.
Mismatched usernames and login names is probably the most common problem that you'll have when moving a SQL Server database to a new location. If you make sure you understand why mismatched names can be a problem and how to avoid the biggest hurdles, you can usually use sp_sidmap to solve the problem.
After you understand the problems involved in restoring a single database, you're ready to learn what to do when one of your system databases has been damaged and you need to regain access to your SQL Server and all your user databases. In an upcoming SQL Server Magazine article, I'll explore what you need to know when you restore an entire SQL Server system, including the system databases master, model, and msdb.
