Find Orphaned Users In SQL Server

Orphan users can occur once you detach databases or restore a database from another SQL Server database instance. The reason for this is that whenever a user is created, a login name and SID will be created. Each database user is mapped with an SID. This SID is different from one SQL Server instance to another. So in case, you restored your databases from some another SQL Server instance, the SID in the database will match with the SID of the login in the current server. You can view un-matching users from following code:

EXEC sp_change_users_login 'REPORT'

So you can match them by providing following script.

EXEC sp_change_users_login 'Update_one',@UserName,@UserName

Also, you can use following script from SQL Server 2005 SP2 onwards.

ALTER USER
dbusername
WITH LOGIN = Loginname

However, what if you have numerous users in your database? Running the script for each user may be a tedious task. The following script will automatically highlight the orphan users. However, this script will assume that database user and login names are the same.

/*
Author: Dinesh Asanka
Purpose: Fixing orgphan users
Date: 2012-07-07
Warning: Author is not liable for any after issues with this
script
*/
SET NOCOUNT ON
CREATE TABLE #orphan_users
(UserName varchar(50),
UserSID varchar(1000)
)
INSERT INTO #orphan_users
EXEC sp_change_users_login 'REPORT'
ALTER TABLE #orphan_users DROP COLUMN UserSID
ALTER TABLE #orphan_users ADD IsProcessed tinyint
Declare @UserName varchar(20)
WHILE (SELECT COUNT(1) From #orphan_users Where IsProcessed IS
NULL ) > 0
BEGIN
SELECT Top 1 @UserName = UserName
From #orphan_users Where IsProcessed IS NULL
Update #orphan_users
SET IsProcessed = 0
WHERE UserName = @UserName
EXEC sp_change_users_login 'Update_one',@UserName,@UserName
END
DROP TABLE #orphan_users

Can we avoid this? Rather than allowing an SID to be automatically assigned, you can provide it:

CREATE LOGIN loginname
WITH
PASSWORD = 'Pa$$w0rd',
SID = 0x4B96EFEDC3BD324BB528A387B15839BF

In above case, all your servers will have the same SID for users and restoring databases across SQL Server instances won’t be an issue.

]]>