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.

Then run:

EXEC 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.