This happens to me all the time. I get a request from a developer to overwrite the test copy of a database with the production copy. Typically this is so they can use real data for testing although there are other reasons.

So first I go around to everyone who might possibly be using that database. I don’t want to overwrite anyone’s test data, or changes they may be working on. I’ve missed this step before, it’s not pretty. Next I take a backup of the old database. Even if you are told “There is nothing we need in the old copy.” this step is important. I’ve had too many cases where they were wrong and there was something important in the test database to miss this step. So last but not least I restore an appropriate backup from prod to test. No need to take a new one because I always have current backups of production. I call my developer and let him know that his database is ready. He tries to log in, and can’t.

So what went wrong? Well there could be several issues, and probably all of them at once.

First let me briefly go over how users (a type of database principal) and logins (a type of server level principal) relate to each other. Each principal has its own SID (security ID I believe). In the case of a windows login this is generated outside of SQL Server, if it’s SQL Login then SQL generates the SID. At the server level (logins) this SID is stored in the master database and can be viewed using the system view sys.server_principals. At the database level (users) the SID in the individual database and can be viewed in the system view sys.database_principals. In order for SQL Server to authenticate an individual they need to connect to the server (login permissions) and then connect to a database (user permissions). The connection between these two layers is the SID. If you follow the logic, this means that when you restore a database from another server the SIDs for windows logins will match and the SIDs for SQL Logins probably won’t match unless you planned for this ahead of time. Note that if you are using a contained database (2012+) then this isn’t true for loginless users.

So what this means is that any users in the database that mapped to SQL Logins probably don’t have a matching login by SID. By extension when you log into the server using one of these IDs SQL Server doesn’t see a matching SID in the database and won’t let you in. This can be particularly confusing for people when the login and user have matching names but still don’t work. Typically you will see this type behavior with application IDs since let’s hope that a) you are using windows logins where possible and b) your developers don’t have the same type of access in production that they do in test.

So how do we find and fix this first problem? Currently my preferred method of finding orphaned users is to use the sp_change_users_login.

EXEC sp_change_users_login 'report'

Unfortunately this particular stored procedure has a little comment in BOL stating it will be removed in a future version. So I wouldn’t get too attached to it. If you run sp_helptext on sp_change_users_login and do a little digging you get the following query that you can use instead.

select UserName = name, UserSID = sid from sysusers
where issqluser = 1 
  and   (sid is not null and sid <> 0x0)
  and   (len(sid) <= 16)
  and   suser_sname(sid) is null
order by name

This is the raw query out of the stored procedure. You could do some work on it to update it without much effort.

To fix the orphaned user with sp_change_users_login I like to do it like this.

EXEC sp_change_users_login 'Auto_Fix', 'LoginName'

However, again, sp_change_users_login is going away, so we should probably get used to this.

ALTER USER UserName WITH LOGIN = LoginName

Ok, so far so good. We have fixed all of the orphaned users that were caused by restoring the database from another server. Unfortunately our users still can’t log in. So what’s wrong now? Well, as I mentioned above, most likely your developers don’t have the same level of access in production (let’s hope) that they do in test.

Having gotten to this point you have a couple of options. The messiest is to try to rebuild the permissions based on what the users and developers tell you. I don’t recommend this since they probably don’t particularly their permissions any better than you do. Next down on the painful list (in my opinion) is keeping a security document. Everyone who has permission is on the document and what permissions they have are listed. This would be great during an Audit but a real beating to maintain. Next (and what I usually do) you can either restore the backup you took earlier (you did take one right?) to a different location and script out the permissions or you can make a point of scripting out the permissions ahead of time. I recommend scripting out the permissions ahead of time. Then of course apply the scripts. At this point your permissions should be identical to what they were before and your users/developers should have no problems getting into the database.

There is one last method that I know of but it is not one I’ve ever tried. It sounds good though. Create roles for Developers, etc and put them at all SDLC levels. So for example the role for developers will exist in production. Just don’t assign anyone to the role at an inappropriate level. So even though the developer and tester roles exist in production they have no members. On the test instance the tester roles have members but the production and development roles do not. Then create AD groups that match each of the roles. From there it’s a simple matter of maintaining the group membership in AD and matching up the appropriate AD group to the appropriate database role on the appropriate server. Like I said, this isn’t a method I’ve had the opportunity to try but it certainly sounds good.