How to troubleshoot orphan users in SQL Server databases?Last
updated: July 30th '02 |Best viewed with: All popular
browsers | Best viewed at:1024x768 |Links to external
sites will open in a new window

The most common reason for ending up with orphan users is, moving databases from one server to another or from one domain to another. Restoring an old copy of master database can also result in this problem. Moving databases between servers is quite a common task. So, what's exactly resulting in orphan users?

Well, all SQL Server logins are stored in the system table sysxlogins (in master database). Whenever you create a new login (or grant access to a Windows account), a corresponding entry gets added to the sysxlogins table. So, you could say, each login has a corresponding row in sysxlogins table. In sysxlogins, each login has an associated SID (Security Identifier), which is of type varbinary(85).

So far so good. Every database contains a table called sysusers, which stores a list of all users who have access to that database. That is, sysusers actually contains a list of logins that have been granted access to this database. So, whenever you grant access to a login into a particular database, an entry gets added into the sysusers table of that database. This entry refers to the login in sysxlogins, using the SID column. Based on this SID linking, SQL Server can map all database users to their corresponding logins.
Problem arises when you move (using backup/restore or detach/attach) a database to a new server. Since sysusers table is stored within the database, it gets moved over to the new server as well. Now the new server might or might not have the same logins, and the SIDs of these logins could be completely different from the SIDs of these logins in the original server. What this means is that, the sysusers table in the newly moved database has SIDs that aren't anymore there in the sysxlogins table on this new server. So, SQL Server can not map the users in this database to any of the logins. That's what results in orphaned users.

You may not realize the orphan users problem immediately, especially if you login using a administrator account or a login that has sysadmin privileges. The most common symptoms are:

- Applications will experience 'login failed' error messages and fail to log into this database.

- Users won't show up in Enterprise Manager, but when you try to add users, you will get error messages saying 'User or role already exists in the current database'

To overcome this problem, you need to link the SIDs of users (from sysusers) to the SIDs of valid logins in the master..sysxlogins table.

The following pages from SQL Server Books Online and links from Microsoft Knowledgebase will help you understand, troubleshoot and fix this problem:

sp_change_users_login : Use this stored procedure to see and fix orphaned users. Books Online has complete documentation and examples

sp_validatelogins : This stored procedure reports information about orphaned Microsoft Windows NT/2000 users and groups that no longer exist in the Windows NT environment but still have entries in the Microsoft SQL Server system tables. Complete documentation is available in Books Online.

- It will not report orphaned users belonging to Windows accounts. (In this case, it is better to drop the user using sp_revokedbaccess, and readd the user using sp_grantdbaccess)

- It will not report/fix orphaned dbo user (Orphaned dbo user can be fixed by using sp_changedbowner. This will change/update the owner of a database and associate the owning login to the dbo user).

To overcome the above problems, I wrote my own stored procedure ShowOrphanUsers, that loops through all the databases and identifies all orphaned users. ShowOrphanUsers output contains two columns: The database name and the orphaned user name. We use it extensively at my work place, whenever we build new servers, or move databases between different servers and domains. It takes no input parameters. It is tested on SQL Server 2000. To get this working on SQL Server 7.0, simply remove all references to COLLATE and compile the procedure. By default, this procedure will not check the following databases for orphaned users: master, model, tempdb, msdb, distribution, pubs and northwind. If you wish to verify any of these databases, simply remove that database name from the NOT IN list, by editing the stored procedure. As the name ShowOrphanUsers indicates, it only shows the list of orphaned users! You will still have to use sp_change_users_login to fix these orphaned users.

Note: I referred to the system table sysxlogins through out this article, but this system table is not documented. Querying the system tables is not a recommended approach, but if you do want to query sysxlogins, query syslogins instead, which is a view on top of sysxlogins and is documented.