This post is for me. It's about something I am always forgetting.
In Microsoft SQL Server, a database server has logins and each database has users. Every user is associated to one login. If you detach a database from one server and move it to another (or, equivalently, backup and restore to a different machine) then your user is no longer associated to a valid login. This happens even if you have a login on the new machine with the same name as on the old machine.
Effect? No access.
It's easy to fix, though. As sa, run this stored procedure in the affected database:
SP_CHANGE_USERS_LOGIN UPDATE_ONE, 'username', 'login_name'