belle’s sql musings

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

Posted in Issues/Troubleshooting by belle on June 12, 2008

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

10 Responses

Subscribe to comments with RSS.

  1. Custard said, on December 10, 2008 at 8:29 pm

    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.

  2. Custard said, on December 10, 2008 at 8:31 pm

    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…)

  3. Daniel Clarke said, on November 24, 2009 at 9:56 am

    Perfect. You da man.

  4. Kerr S said, on February 12, 2010 at 7:13 am

    Thanks, Custard, for the stored procedures. I much prefer this option where I can script and test easily.

  5. Kwex said, on May 21, 2010 at 7:58 am

    Nice post … it sure did solve my problem!

  6. Chris Duva said, on November 28, 2010 at 8:01 am

    Thanks.
    I didn’t encounter this problem until I moved a web application to IIS 7.5 on a Server 2008R2 VM with the database still on a Server 2003 VM in SQL Server 2005. On IIS 6 it was fine.

  7. Siva said, on March 31, 2011 at 2:26 am

    Thanks… 🙂

  8. Paige Watson said, on August 30, 2011 at 3:07 pm

    Thank you for this.

    I was making a connection to a view in a database that returned this error. The issue with my connection was that the view referenced another database, and while I could see the main database, I had no rights to the referenced database.

    Once I added a mapping to the referenced database, it worked.

  9. Yomi said, on July 18, 2012 at 5:16 am

    Best on the web. Thanks for the solution…..

  10. Brendan said, on February 13, 2013 at 8:33 am

    Saved me pulling my hair out, was trying to connect a user to a arcsde database and it kept giving me bad login message. I got the above message when I went into SSMS on sql server 2005. Followed your instructions and user can now connect


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

%d bloggers like this: