If you move a database from one server to another using backup\restore or detach/attach option then there is a chance of users become orphaned. Users logins mapping done by the SID and if there is a login present on the destination instance with same name but another SID then the user will not recognize and orphaned. Login mapping problem occurs only with SQL Logins when you move the database to another server on same domain. You would not find any problem in migrating widows logins as their SID is same as domain SID set in active directory.
Follow the below steps to identify and correct the orphaned users in restored or attached database
1. Identify Orphaned SQL users using below query
1: SELECT dp.name, dp.sid
2: FROM sys.database_principals dp
3: LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
4: WHERE sp.sid IS NULL AND
5: dp.type_desc = 'SQL_USER' AND
6: dp.principal_id > 4;
2. Use the below command to re-map user to an existing login
ALTER USER Fred WITH LOGIN = Fred
