It’s a problem almost every MS SQL Server DBA has encountered at least once. You restore a DB that was on one machine to another machine. All the users exist in the DB, but you can not log in as those users (even if they exist on the new machine). Maybe you’ve deleted all the users from the DB then added them back, or maybe you’ve used the following functionality.
First of all, to be certain that orphaned users are the problem run the following command:
EXEC sp_change_users_login ‘Report’
Next, if you already have a LoginID and Password at the SQL Server level, you can fix it by running the following command:
EXEC sp_change_users_login ‘Auto_Fix’, ‘UserId‘
If you need to create a new LoginID and Password for this user, you can run the following command:
EXEC sp_change_users_login ‘Auto_Fix’, ‘user’, ‘UserID‘, ‘Password‘
Essentially what has happened to your users is a simple issue. Behind the scenes in SQL Server each user has a hidden GUID. This GUID is not going ot be the same across servers, so the GUID in the DB is looking at the wrong (or missing) GUID at the server level.