I recently was sent a backup file to restore by a consultant. The file had logins I had already defined as users of the database. When I restored the backup, I was unable to sign in with a known user:
C:\>osql -S SERVER01 -d TestDB -U JSmith Password: Cannot open database requested in login 'TestDB'. Login fails.
My login, JSmith, had become orphaned. This means that, although I had a login named JSmith, the internal representation of JSmith in my database did not match the internal representation in the backup.
There is a simple piece of code to fix this:
EXEC sp_change_users_login 'Auto_Fix', 'JSmith'
If this works, you will get a message like this:
The number of orphaned users fixed by updating users was 1.
I did this and my login started to work again.