This is the difference between logins and users and how they relate to each other:
- Logins - Instance level principals that allow an entity to connect to the SQL Server instance. They do not, by their nature, grant any access to databases on the instance. The exception to this is a login with sysadmin rights can use a database because they are sysadmin, but because of sysadmin level permissions.
- Users - Database level principals that allow an entity to connect to a SQL Server database. Users are associated with logins via SIDs, creating a relationship between the two and allowing a login to connect to the instance and then use the associated user to connect to the database.
What commonly happens with SQL authenticated logins and database users on a restore is that the SIDS will be out of sync, thus breaking the relationship. This relationship must be repaired before you can connect to the database using that login, because in the eyes of SQL Server those principals are no longer connected. You can fix this with the following SQL:
ALTER USER [foo] WITH LOGIN=[foo]
You can use the following query in the context of your database to check for orphans:
left join sys.server_principals sp on (dp.sid = sp.sid)
dp.type in ('S','U','G')
and dp.principal_id >4
order by sp.name