When restoring a SQL Server backup, the users that are restored in the database are not the same as the users that were created at the server level, even though they may share the same name. This creates a problem with websites/users/etc that try and log into these databases. Because the accounts aren't the same, the login is rejected.
There are several ways to fix this problem. Sometimes it is easiest to just delete the account out of the database and recreate the account at the server level. This only works when the user account in question hasn't created any of the objects in the database.
If the account has created objects in the database, then create a new server login with the same name as the database login. Run the stored procedure:
sp_change_users_login 'update_one', 'username', 'username'
This remaps the accounts to be the same based off of the name.
For more info, see:
http://support.microsoft.com/kb/274188