Why can't I CREATE or DROP a USER in a database restored from another server?

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.

SQL BOL: Troubleshooting Orphaned USERS
http://msdn.microsoft.com/library/en-us/trblsql/tr_servdatabse_0ttf.asp

MS KB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q274188

MS KB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q168001

MS KB: How to Resolve Permission Issues When a Database is Moved Between SQL Servers
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q240872

MS KB: How To Transfer Logins and Passwords Between SQL Servers
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q246133

SQL BOL: sp_change_users_login
http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_ca-cz_8qzy.asp

Database Joural: Fixing broken LOGINS and transferring passwords
http://www.swynk.com/friends/boyle/fixingbrokenLOGINS.asp

Database Journal: How to detect & rectify Orphaned Users in a Database
http://www.swynk.com/friends/dhingra/howtos3.asp

MS KB: Using the Auto_Fix Option with sp_change_users_login Can Leave Security Vulnerabilities
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q298758

Keywords: sp_addlogin, sp_adduser, sp_dropuser, sp_change_users_login, orphaned users, move databases, permissions, broken logins, transfer logins, passwords