Issue:
We backed up and restored one database from one server to another. Sounds simple, right? Should be. This isn’t one of those days
So we get this issue:
The server principal “myuser” is not able to access the database “mydb” under the current security context.
Resolution:
Thanks to the following forums, we were able to troubleshoot. Steps follow:
1. In order to ‘Map’ the Login, the Login must not already be as User on the database.
Go to ServerName -> Databases -> DatabaseName -> Security -> Users
2. Delete the Login from the list of database Users before mapping the Login to the database.
3. In Object Explorer -> Server -> Security -> Logins, right click and choose Properties
4. The ‘User Mapping’ page should list all databases on the server with a check mark on the databases that the Login has been mapped to.
Map the Login by checking the box next to the database name.
You may also need to add some fixed database roles, just to get your stuff working first:
ie, db_datareader and public.
5. Click OK
6. Rejoice
References:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1638146&SiteID=17
http://www.sqlservercentral.com/Forums/Topic362018-359-1.aspx