Handling Orphaned Database Users

CrucisCo
2 Oct 2008
0

This isn't the first time I've had to deal with orphaned users in SQL Server, and probably won't be the last but today I found myself investigating why it happens and how to resolve the issue it causes.

I guess before I start on that I should explain what an orphaned user is. The best way is probably to illustrate with a common scenario. You want to make a copy of your database on a different server. So you make a backup of your database as it exists on your current SQL Server. This server and the database have been configure with a bunch of logins and database users, and your intention is to use this same configuration of security on the target server.

I guess that this is a good time to explain a common point of confusion: login and user. Used interchangebly in many cases in IT - but in SQL Server they are quite different. The login is an account created on the server to enable a user to authenticate on that server. Every login is associated with a Security IDentification (SID) in the master..syslogins table. That login satisfies the authentication part of security.

The user on the other hand is an instrument of authorisation. It is associated with a login in SQL server so that a connection to the server can be authenticated before it is authorised to access (or not) a database or parts of a database.

Okay, so back to the problem, when you restore your database you find that it will create the users under your database - but will not create the underlying login - the thing that actually allows connections to authenticate with your new server. So you then create new logins on the new server with exactly same passwords and properties as the old server. Result - you still don't get anywhere.

The problem is that even though you have created the same logins, the association between the users restored and the login that you've just recreated does not exist - the SIDs don't match.

Normally, when I encounter this problem I simply delete the users in the restored database and then add them via my new login. But this does not always work because when I tried today to delete my users I could not. The problem was that the user in question was the database owner and SQL will not let you delete a database owner. You get the following errors depending upon whether you are using SQL 2000 or 2005:

So that leads me on to the eventual resolution which is more elegant than the brute force 'delete and create' method.

Execute the following query to get a listing of all the orphaned users in your database.


USE ;
GO; 
sp_change_users_login @Action='Report';
GO;

Then for each of these, you can associate your new login with the existing user by running the following:


USE ;
GO
sp_change_users_login @Action='update_one', @UserNamePattern='', 
   @LoginName='';
GO

EDIT 04 Aug 2011: There is an easier way to fix the orphans using the Auto-fix option.


USE ;
GO
sp_change_users_login @Action='Auto_Fix', @UserNamePattern='', 
   @LoginName='', @Password='';
GO

There are a number of caveats to using this:

If the login name will be assumed to be the same as the database user
If the login exists, the @LoginName parameter must be NULL. @Password will be ignored
If the login does not exist, it will be created (same as database user name) and @Password must be provided.
Always examine the output of the statement to ensure that the correct link (login to database user) is made