The Scenario:
You just restored a production database on a development server. You’ve told the developers that it’s restored, and you start to relax in your chair. 30 milli-seconds later, your phone is ringing… the developers can’t connect to the database that you just restored. You check the server, and the login exists. You check the database, and the user exists. You start scratching your head… maybe even banging your head against the wall.
The Problem: You’ve got Orphaned Users
Books Online, in the Troubleshoot Orphaned Users section, describes an orphaned user as:
A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be an orphaned user of the database on that server instance. A database user can become orphaned if the corresponding SQL Server login is dropped. Also, a database user can become orphaned after a database is restored or attached to a different instance of SQL Server. Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance.
What you have encountered is what is described in the last two sentences. The login on the production server has a specific SID, and when it was added to the database as a user, that SID was used. However, when you restored that production database onto the development server, the login on the development server has a different SID – even though the names are the same.
Fixing the Orphaned Users
With the following script in your toolbox, you’ll be ready to fix these orphaned users:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
-- find all of the orphaned (SQL) users (DB SQL User without a login with the same SID) WITH cte AS ( SELECT dp.name AS DBUser, dp.sid AS DBSid FROM sys.database_principals dp LEFT OUTER JOIN sys.server_principals sp ON dp.sid = sp.sid WHERE sp.sid IS NULL AND dp.type = 'S' -- SQL_USER AND dp.principal_id > 4 ) -- join to logins with the same name to get the sql statement to un-orphan the user SELECT cte.*, sp.name, -- generate the SQL script to un-orphan the users that can be. LinkOrphanUserToLoginSQL = N'ALTER USER [' + cte.DBUser + N'] WITH LOGIN=' + sp.name + N';' FROM cte -- LEFT JOIN allows seeing all orphaned users LEFT JOIN sys.server_principals sp ON cte.DBUser = sp.name AND sp.type = 'S' ORDER BY DBUser; |
Now, this script doesn’t fix the orphaned users – but it does generate the SQL statement for you to fix them. It first finds the orphaned users by finding database users (database_principals) that do not have a matching SID in the server logins (server_principals), and where the user type is a SQL USER (type = S). It then joins this result set back to the server_principals based on the name, and generates the ALTER USER script for each found user that will associate the user with the login.
You may notice that I’m not automatically fixing these users, which would be pretty simple to do with a cursor. I’m not doing this automatically because you may have a loginless user by design on this system. And I always like to do a two-phase approach to things, just to give you one more chance to review what you’re doing and question it.
(Perhaps you noticed in the BOL section that it uses the sp_change_users_login system stored procedure to check for and fix orphaned users. Why didn’t I use it in the script? There are two reasons: 1. It doesn’t generate the SQL script to fix the problem, and 2. it’s deprecated.)
Really fixing the problem
After several times of doing this, you’re getting tired of having to remember to whip out this script after every restore… you just want it to work without any additional work on your part (after all, as a DBA, you want to spend your time out on the golf course or shooting hoops, not doing mundane stuff like this).
As mentioned previously, the problem is that the SID between the server login and the database user doesn’t match up. The solution is to simply make them match up. Thankfully, Microsoft has provided a script to us that we can use… sp_help_revlogin. This script creates two system stored procedures (sp_hexadecimal and sp_help_revlogin). When you run this script, it creates a CREATE USER statement with a hashed password (so that even you can’t see what the password is)… and the SID to use for this user. This will allow you to keep the SID the same between different servers. And now, when you restore that database from production to development, the SIDs match up and everyone is happy. Especially you, since you’re now out on the course that much faster…
Reminder:
When restoring a database from one environment to another, always take a couple of minutes to check for orphaned users and fix them.
Update #1: just to be complete, if you have a SQL Server 2000 instance (I’m sorry), here is the sp_help_revlogin script for that version.
Update #2: I’ve added a modified version of sp_help_revlogin and helper scripts in my Code Library.
I noticed a small type. I believe you meant “This script creates two system stored procedures (sp_hexadecimal and sp_help_revlogin).” Good post. I didn’t know about these.
Reply
Hi Jeremy,
Thanks for the sharp eye. I’ve fixed the post.
Reply