I’ve refreshed a test database from prod but now the users can’t access it.

4

October 9, 2013 by Kenneth Fisher

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.

4 thoughts on “I’ve refreshed a test database from prod but now the users can’t access it.

  1. Chuck says:

    I’ve had a similar situation with a log shipped secondary database that was in standby mode. To allow SQL authenticated logins to query the secondary database I had to create the SQL logins on that server using the “WITH SID” option and manually specify the SID. Since the secondary databases themselves cannot be modified with sp_change_users_login (or any command for that matter), the problem needs to be addressed on the login side of the equation.

    • Excellent point. I’ve never had any real experience with log shipping so I didn’t think of that. I imagine the same holds true for replication, mirroring, always on etc. Anything that puts a copy of the database onto another server.

  2. Brian Parker says:

    What Chuck suggests can be useful in any environment where you regularly copy a database from one server to another and need working SQL logins to come with it. Microsoft provides a base script ( http://support.microsoft.com/kb/918992 ) to bring over both SID and password with a login. I added tweaks to let you optionally script “drop if exists,” include windows logins, etc… probably ought to put my version online somewhere, but Microsoft’s will get you started. When the logins exist with the right SID (and password), the restored database works as expected!

  3. […] project. And as anyone who’s done many of these types of moves knows, orphans are a big pain. You backup your database from one server and restore it to another and all of a sudden no one can lo… Multiple that by ten, twenty, a hundred, and this can be a real problem. There are easy ways to […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,146 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: