SQL Server Reference Guide

Here’s the situation: You’ve taken a backup of a database, and you’ve copied it to another server. Database Administrators do this all the time — to have a production copy of data, to have a reporting database or for development and testing, or just to have a “warm standby” copy of the database ready to go.

But when you copy the database over to the other server, sometimes users can’t log on. In fact, they might get the error message:

Or even this one:

Or even worse — the user gets in, but suddenly has more (or less) permissions than they used to! What’s going on here?

Using the troubleshooting checklist I described in another tutorial, you’ve identified the components in the system, and the only thing that has changed is the move itself. The user is still the user, the data is still the data. You don’t have any other issues, no hardware problems, and everything else seems to be working fine. Your next step is to try another user login to see if they also have the issue — and sure enough, they do. The only common element is that “new” database copy.

You begin your investigation by checking the logins on the server. As you’ll recall from my discussion on security, there are actually two logins to a SQL Server database — one for the server, and then another for the database. You open SQL Server Enterprise Manager (SQL Server 2000 and lower) or SQL Server Management Studio (SQL Server 2005 and higher) and check the server logins on the second, or destination server. Sure enough, all your users are there. Next you drill down to the databases, and – wait a minute — the users are NOT there. This must be the problem.

So you right-click the Security node beneath the database name and select New User from the menu that pops up and you try to create the user. But when you do, you get one of these messages:

or:

or even:

So you try to use the sp_adduser Stored Procedure to create the user, or in SQL Server 2005 and higher the CREATE USER statement. Same errors!

OK, if they are already in the database (although they aren’t shown anywhere), you decide to delete the account in the database and re-add them. That’s a sound strategy. But when you try the sp_dropuser or DROP USER commands, you get the same error! What’s going on here?

The Problem

This problem is created because of the way SQL Server handles logins to the server and database, and the fact that Microsoft followed good database design principles. That’s right, because of Relational Database theory you’ve now got what is called an “orphaned login”. You’ll see why in a moment.

I’ll create a database, server login and database user on two test systems to show you the process, and if you like, you can follow along on your test systems as well. Don’t do this in production, of course!

On the first system, I’ll create a test database by running this code — I’m using SQL Server 2008, but this will work with SQL Server 2005 as well. For SQL Server 2000, It’s only a matter of using a different set of table names that I’ll point out in a moment.

CREATE DATABASE Orphans;
GO

Now I’ll create a new user (for SQL Server 2000, you use the sp_addlogin Stored Procedure):

USE [master]
GO
CREATE LOGIN [Bob] 
WITH PASSWORD=N'Pass@word1'
, DEFAULT_DATABASE=[Orphans]
, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

So far, so good. Now I’ll change over to that database and add the server user to that database, and put the login in the db_datareader role so that they have the right to view data:

USE [Orphans]
GO
CREATE USER [Bob] FOR LOGIN [Bob]
GO
USE [Orphans]
GO
EXEC sp_addrolemember N'db_datareader'
, N'Bob'
GO

Now, I’ll backup the database to my TEMP directory, and then copy the backup file to my other server:

USE master;
GO
BACKUP DATABASE Orphans 
TO DISK = 'c:\temp\Orphans.bak' 
WITH INIT;
GO

I’ll now log in to my second server, and restore the backup there. Since the driver letters and directories are the same on both test systems, that’s a pretty simple command – again, this is run on the second server after I copy the backup file over to its TEMP directory:

RESTORE DATABASE Orphans
FROM DISK = 'c:\temp\Orphans.bak';
GO

Of course, you can do all this with the graphical tools as well, but I rely on scripts a great deal and of course they are more consistent in an explanation like this. Now, once again on the second server, I‘ll create that Bob login there:

USE [master]
GO
CREATE LOGIN [Bob] 
WITH PASSWORD=N'Pass@word1'
, DEFAULT_DATABASE=[Orphans]
, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

Now all that’s left is to tell Bob to log into his application. He logs in, and gets the dreaded error messages from above. So I look in the graphical tools for Bob’s login in the database Security node, and sure enough, it isn’t shown. No worries, I’ll just create him on that second database:

USE [Orphans]
GO
CREATE USER [Bob] FOR LOGIN [Bob]
GO
USE [Orphans]
GO
EXEC sp_addrolemember N'db_datareader'
, N'Bob'
GO

Yikes! I get this message:

OK, now I’ve duplicated your problem, and we can spend some time on why it happens.

First, over on the original server, when I created the server user an entry was placed in the sys.syslogins table (or the sysxlogins table in SQL Server 2000 and lower). I can see that with this query, on the first server:

SELECT * 
FROM sys.syslogins
WHERE name = 'Bob';
GO

Right there in the “name” column I see Bob. Remember from my series on SQL Server Security that you have the two logins – one to the server, the other to the database. So now I’ll look in the Orphans database for the logon there:

USE Orphans;
GO

SELECT *
FROM sys.sysusers
WHERE name = 'Bob'
GO

And there’s i. Now, the key here is the Key. That is, just like you should do, Microsoft doesn’t rely on the “name” column to tie the sys.syslogins (server logins) and the sys.sysusers (database logons, sysxusers for SQL Server 2000) together. It uses a key — in this case, a field called “sid” (for Security Identifier). You can see that when I tie Bob’s info together with this query:

SELECT a.name AS 'Server Name'
, a.sid AS 'Server ID'
, b.name AS 'Database Name'
, b.sid AS 'Database ID'
FROM master.sys.syslogins a
INNER JOIN Orphans.sys.sysusers b
ON a.sid = b.sid
WHERE a.name = 'Bob';
GO

In fact, this is exactly what the graphical tool is doing to show you the users in the database — it runs a query just like this one. You can see that the two sid’s, from the server and database queries, are identical, and you get data back from the query.

Now I’ll switch over to the second server to run exactly the same three queries. The first one, from the server logins (master.sys.syslogins) shows me this sid for the Bob login in my environment:

0x3618F8C27389784DBEDFED5EE811FF58

But the database logins (Orphans.sys.sysusers) has this sid:

0xC158D6E576DF3C46B30582BD891A4332

Ah — when I look carefully, I see that the sid in the database came over with the backup! That’s the problem — the master database on the first server has the same sid value as the Orphans database values, but the master database on my second system has a different sid for Bob (even though the name is the same) than what came over in the backup file.

So if that’s the case, why can’t I just drop the Bob user in the database and recreate it? Well, there are two reasons. For one, the logins are tied, so when the graphical tools and functions try to delete the user, they check the name field in the master database and find that there is a match, so they fail. They don’t want to create an “orphaned” user this way.

The second reason you don’t want to just drop the login is that it probably has a lot of security tied to it — it might own objects, it has rights to read or write to various tables, it has permissions on Stored Procedures and so on. Multiply that by dozens of users, and you’ve got a lot of work to do if it goes away — plus, you could get all that wrong and really cause an issue.

The Fix

Now that you understand the problem, how do you fix it? It actually all comes down to just making the sid’s on the two databases, master and Orphans, identical. Simple, right?

Well, not really. As you’re probably aware, the “sys” schema I’ve been using here (and the sysxlogins tables in SQL Server 2000 and lower) are actually not accessible, for very good reasons, to be easily modified. You can’t just use an UPDATE statement against the tables.

Microsoft has provided an easier fix, which works in most situations. I’ll give you a link at the bottom for those situations that are a little more difficult or involved, but you should try this solution first.

It’s a simple stored procedure, which has three “modes”. The first is to report any problems, the second is to fix a single login, and the other is to fix any logins it can automatically. Let’s take a quick look at each.

The stored procedure is called sp_change_users_login. I’ll run it in the first mode to see if it catches the Bob orphaned login on the second server. I need to be in the database context that I want to check, so first I’ll use the Orphans database. From there, I just run the Stored Procedure and give it one parameter: Report. It looks like this:

USE Orphans;
GO
EXEC sp_change_users_login 'Report';
GO

It finds that the Bob login is indeed orphaned — it exists in both databases, but with the wrong sid. To fix the problem, I could use another option for the stored Procedure by sending the “Auto” parameter, like this:

USE Orphans;
GO
EXEC sp_change_users_login 'Auto_Fix';
GO

The Auto parameter simply looks for all the same names in the master database and the database you’re in and ties them together. I don’t normally use this method — I find the orphans and then fix them one at a time. It takes longer, but unless I have thousands of logins, I like having this level of control. To fix just one login, I use two parameters — the database login name and the server login name that I want to fix, like this:

USE Orphans;
GO
EXEC sp_change_users_login 'Update_one’, ‘Bob’, ‘Bob’;
GO

And there you have it — all fixed up. I can run the query that joins the two databases again, and now I see that Bob shows up, and also that the database logon shows up in the graphical tools as well.

You can take pro-active steps to prevent this from happening in the future. First, you can use Database Roles for the security, which is a best practice, and then just run a set of database logon scripts to place the users in the right groups. Database Roles are kept within the database, so you don’t have to worry about that server connection.

Another method is to “build” the database rather than copying it. From then on, you just use Microsoft Data Transformation Services (DTS) or SQL Server Integration Services (SSIS) to transfer the data and database objects to keep them in synch. This is the method I use for my reporting, testing and development servers.

InformIT Articles and Sample Chapters

If you’re interested in learning more about Database Roles, I cover that in this overview: SQL Server Security – Roles.

Books and eBooks

Security is a big topic, and I cover it in more depth in my Administrator’s Guide to SQL Server 2005. It’s still pretty valid even for SQL Server 2008.

Online Resources

There are a few restrictions on the stored procedures and methods I’ve shown you in this tutorial. If these processes do not work for you, then check here for more information.