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 examplesTroubleshooting Orphaned Users : Refer to this section in SQL Server Books Online for troubleshooting tips.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.

Q168001 PRB: User Logon and/or Permission Errors After Restoring Dump

Q274188 PRB: “Troubleshooting Orphaned Users” Topic in Books Online is Incomplete

Q246133 INF: Transfer Logins and Passwords Between SQL 7.0 or SQL 2000 Servers

Q240872 How to Resolve Permission Issues When a Database Is Moved Between SQL Servers

As I mentioned above, we need to use sp_change_users_login to identify and fix orphaned users. But sp_change_users_login has the following limitations: – It can only show orphaned users from the current database.

– 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.

Here is the stored procedure code:

CREATE PROC dbo.ShowOrphanUsers
AS
BEGIN
CREATE TABLE #Results
(
[Database Name] sysname COLLATE Latin1_General_CI_AS,
[Orphaned User] sysname COLLATE Latin1_General_CI_AS
)

SET NOCOUNT ON

DECLARE @DBName sysname, @Qry nvarchar(4000)

SET @Qry = ''
SET @DBName = ''

WHILE @DBName IS NOT NULL
BEGIN
SET @DBName =
(
SELECT MIN(name)
FROM master..sysdatabases
WHERE 	name NOT IN
(
'master', 'model', 'tempdb', 'msdb',
'distribution', 'pubs', 'northwind'
)
AND DATABASEPROPERTY(name, 'IsOffline') = 0
AND DATABASEPROPERTY(name, 'IsSuspect') = 0
AND name > @DBName
)

IF @DBName IS NULL BREAK

SET @Qry = '	SELECT ''' + @DBName + ''' AS [Database Name],
CAST(name AS sysname) COLLATE Latin1_General_CI_AS  AS [Orphaned User]
FROM ' + QUOTENAME(@DBName) + '..sysusers su
WHERE su.islogin = 1
AND su.name <> ''guest''
AND NOT EXISTS
(
SELECT 1
FROM master..sysxlogins sl
WHERE su.sid = sl.sid
)'

INSERT INTO #Results EXEC (@Qry)
END

SELECT *
FROM #Results
ORDER BY [Database Name], [Orphaned User]
END
 

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.