MS SQL server: How to troubleshoot orphan users in SQL Server databases?

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.

use my_db_name
go
exec sp_change_users_login 'Auto_Fix', 'User_name'

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.