Fixing Orphan Users....

Attaching and restoring databases from one server instance to another are common tasks executed by a DBA. After attaching or restoring of a database, previously created and configured logins in that database do not provide access. When you restore a Microsoft SQL Server database on a different machine, you cannot access the database until you fix the permissions.

The problem is that the user in the database is an “orphan”. This means that there is no login id or password associated with the user. This is true even if there is a login id that matches the user, since there is a SID that does not match.

First, to detect orphaned users. This will lists the orphaned users:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, you can do either of the below.

EXEC sp_change_users_login 'Auto_Fix', 'user'

EXEC sp_change_users_login @Action='update_one', 
@UserNamePattern='TestUser1', 
@LoginName='TestUser1'

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'