What is an orphaned user?

SQL Server maintains user logins in the sysxlogins system table in the master database. Every time you add a user, or give a Windows account access, an entry is made in this table. Every user is assigned a security identifier (SID) in sysxusers. Each database has a table called sysusers that contains a list of users that have access to that database. Typically, the SID in the sysusers table matches the SID in sysxusers.

When a user entry exists in sysusers that that does not have a matching SID in sysxusers, that user is “orphaned.” If you use SQL Server Management Studio to create a user with the same userid as the orphaned user, you cannot grant the user access to the database. The new user will get a different SID.

What causes orphaned users?

Typically orphaned users occur when moving a database from one server to another. The new server either does not have the existing users, or the users’ SIDs do not match the ones from the old server. Orphaned users can also occur when restoring an older version of the master database.

How do you fix orphaned users?

SQL Server provides the sp_change_users_login stored procedure to help fix orphaned users. (See SQL Server Books Online for full documentation.) To run the stored procedure, open SQL Server Management Studio (Express version works too) and create a new query. Set the database to the one with the orphaned users and execute the following once for each user that needs to be fixed. Be sure to replace ‘USER’ with the actual user id.

exec sp_change_users_login 'auto_fix', 'USER'