SQL Server Permissions – Fixing Orphaned Users
If you move a SQL Server database to another server, you frequently bump into problems with users and security. The most common scenario is that you’ve created the user under Security, and the user of course exists within the database that you moved, but when you try to apply user permissions, you get an error like this:
Error 15023: User or role '%s' already exists in the current database.
That’s a byproduct of the fact that SQL Server uses special magic glue in the background to associate user accounts in a database with logins on the server. There’s a quick fix for it:
sp_change_users_login 'auto_fix', '[UserName]'
(where [UserName] is the name of a user to be fixed)
More information and a more exhaustive outline is available in this short article by Megan Forbes. Microsoft also has a Knowledgebase article that explains the problem and a somewhat different solution (in English with a heavy technical dialect): Q240872.