Whenever I restore a production database to a development server, I often end up with orphaned users. You can see in the screen shot below that username matt has no login associated with it.
An account becomes orphaned when there is a user account with security rights in the database that is not linked to a user account that can log in to the server.¬† This happens because SQL logins with the same username have different SID’s on different servers.¬† It is not a problem for Active Directory logins because the SID is stored in Active Directory.
Once an account becomes orphaned, it is a pretty easy fix:
USE <database name> ALTER USER <username> WITH LOGIN = <username>
But it’s still a pain to remember to run this code to reset the logins.¬† I usually forget, and the scratch my head for a minute to figure out why the application won’t connect to the development database anymore.
Fix it once and for all
Microsoft has a handy script to fix orphaned users (link).¬† Download and run the script on your production server to create two stored procedures: sp_hexadecimal and sp_help_revlogin.
Run the output from this procedure against your development server to create the appropriate logins with the correct username/password and SID.
Ever since I ran this, I have not had any more orphaned users when restoring databases back to dev.