Orphaned Users in SQL Server

It happens all the time - orphaned users. Often times you are required to restore a database for testing purposes and you go back to your land of semicolons, butterflies and ponies and all of a sudden BAM: Login failed for user 'dbuser'.

In SQL Server, Database Users and Server Logins are two different entities. Users are associated to the database level, and logins are associated to the Server level. Every User must be mapped to a Login. Otherwise, you get a dreaded orphan.

In SQL Server 2008, run sp_change_users_login @Action='REPORT' to detected orphaned records. I actually just ran this and there are three orphaned users in one of my databases right now. Slacker!

To resolve an orphaned user, run sp_change_users_login @Action='update_one', @UserNamePattern='DatabaseUserName',
@LoginName='ServerLoginName';

And there you have it. Happily reunited.