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

See also:   Database coding conventions SQL Server interview questions Evaluation of federated database servers SQL Server FAQ on programming, administration, replication and tools SQL Server security best practices SQL Server administration best practices

Related books:

How to troubleshoot orphan users in SQL Server databases?


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:

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