Recently after restoring a database backup which I got from another server I repeatedly encountered an error when logged in using a user who is given permission. But when ‘sa’ account is used I could work with the database without any problem.
The error message was “The database DATABASE NAME is not accessible. (ObjectExplorer)” which was not helpful since it didn’t give any clue to figure out the issue.
Later I found that the issue is because the user in the restored database is not properly mapped to the user in the new server and the resolution for this is to run the stored procedure sp_change_users_login to correct the orphaned user.
- sp_change_users_login ‘update_one’, ‘USER’, ‘LOGIN’ – Links the given user in the current database to the specified login.
- sp_change_users_login ‘auto_fix’, ‘USER’ – Links the given user in the current database to the login having the same name in the current server.
After running this you will be able to access the restored database without any issue using the mentioned database user.
Arjuna's space 
Muchas gracias por haber posteado este tema, justamente tuve el mismo problema y justamente se soluciono gracias este.
Saludos!
Usted es bienvenido. Gracias por comentar.