PHYData DBA (7/8/2013)
What happens if you drop and recreate the account?When you create an Account the SID gets imported from AD, could be something in AD or the SQL instance changed and now the match is gone. Droping and recreating one of the Windows Login accounts to see if it resolves this will let you know if sp_change_users_login might help resolve your issue.
This is the issue I believe. If a database is restored to a new instance, the association between the Instance-level login and the Database-level login is lost because the Security-identifier is different.
The solution can be one of three options:
1. Use contained databases in SQL Server 2012 to store the logins for a single database within a single container.
2. Delete the login in the database and recreate it with in the instance, performing the usual mapping, role assignments etc;
3. Use the following command:
USE DB [DB_NAME]
GO
EXEC sp_change_users_login 'AUTO_FIX', 'username'
GO
Hope something there helps!