Issue: Server principal is not able to access the database under the current security context

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
Follow Up Post:
Follow up on Issue: SQL Server Principal Is Not Able to Access the Database Under the Current Security Context

Advertisements

6 comments

  • After restoring to a different server:
    sp_change_users_login ‘report’

    For any users listed:
    sp_change_users_login ‘auto_fix’, ‘user’

    Note that these commands are run from the context of the restored database.

    Like

  • Note that the single quotes surrounding the parameters have been changed due to the font on this website, so if copy/pasting the above commands, ensure the single quotes are replaced with the correct ones (hope that makes sense…)

    Like

  • Belle, thank you very much for this tip. Out of all the Google hits about this error message, yours was the only helpful tip.

    I still had some errors after this procedure but those were solved by reassigning the user to a specific role. Someone did a restore last week and I guess they didn’t follow my instructions 100%.

    Like

  • That solved my problem. Thanks !

    Like

  • Refer: Custard on December 10th, 2008 8:29 pm (POST)
    Thank you Sir, nothing else was working but your post was just what was needed.

    This was a SQL Server 2008 R2 installation that got all the databases restored before the SP1 was installed. Previously the problem did not occurr on a new SQL Server 2008 R2 installation if the SP1 was installed from the beginning. So that is why this POST was so CORRECT.

    After restoring to a different server:
    sp_change_users_login ‘report’

    For any users listed:
    sp_change_users_login ‘auto_fix’, ‘user’

    Again !!! Thanks !!!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s