Orphan Users in SQL Server 2005

All Sql Server logins are stored in a system base table in master database. Whenever a new user is created, a corresponding entry is added in the system base table. There is a associated login entry for every user in system base table which also stores an associated SID (security identifier).

Key Idea

1. An orphan user is one which does not have an associated login entry in the System Base Tables (syslogins for sql server 2000 and sys.server_principals for sql server 2005+)
2. A user is also rendered orphan if the security identifier of the user does not match with the one stored in the system base tables in the master database

Orphaned users are generally created when you do either of the following:
1. Restore a database backup from one server to another
2. Restore an old copy of master database
3. Accidently remove a login belonging to a user

Reporting Orphaned Users
To see all the orphaned users in the database, execute the following query

EXEC sp_change_users_login 'report'

The orphan users if present will be shown as:

OrphanUser1 0x296D8B7BC71BA7459884FE8C17BFC32B
OrphanUser2 0x5F8AD799262298479F6F15FB07E9B0C6

Fixing Orphaned Users
To fix these orphaned users we have to relink the security identifier of the users with the security identifiers of the logins in the system base table. The below query helps fix and reset the password for the orphaned users

EXEC sp_change_users_login 'auto_fix', 'OrphanUser1', null, 'OrphanUser1'
EXEC sp_change_users_login 'auto_fix', 'OrphanUser2', null, 'OrphanUser2'

The above queries will add the login entries if not already present with the given password. In case the login entries are already present, you can use the shorter version of the above queries to fix the problem

EXEC sp_change_users_login 'auto_fix', 'OrphanUser1'
EXEC sp_change_users_login 'auto_fix', 'OrphanUser2'

Note: You may observe the following error: An invalid parameter or option was specified for procedure 'sys.sp_change_users_login' if you are using 'Auto_Fix' as the action instead of 'auto_fix'

References:
Troubleshooting Orphaned Users
MSDN: System Base Tables
Fixing Orphaned Users