When moving databases from one server to another, a user's LOGIN access to these databases can be broken. Attempting to add a database USER results in a message that the USER already exists. Trying to drop the USER results in a message that the USER doesn't exist. This can be irritating and confusing.
SQL Server USERS are created in each database. Each USER is associated with a server LOGIN that is stored in the master database. The Security Identifier or SID relates the USER and the LOGIN. Under certain conditions the USER SID may not match the LOGIN SID. This can occur if the master database is rebuilt or restored and the LOGINS have to be recreated. It can also happen when databases are moved from one server to another. Though the names may match between the servers, the SIDS probably doesnÆt match. The USERS are referred to as orphaned when their SID doesnÆt match the LOGIN SID.
SQL BOL contains a topic, "Troubleshooting Orphaned USERS" which partially explains how to correct the situation and synchronize USERS to LOGINS. This topic is incomplete and some knowledgebase articles were added to provide additional information. Additional articles about orphaned USERS have been published on other Web sites.