Post reply

Why can't user get into DB?

  • March 7, 2012 at 10:28 am

    #252057

    Hello. I added a Windows user to a user defined database role in a specific DB. That role, usrReadExecute, has select and execute rights in the DB. It's a member of db_datareader. To me that seems like enough to get a user into a DB. But she kept getting "the server principle <login/user> is not able to access the database 'msdb' under the current security context."

    I corroborated this behavior on a test account I created. Same thing, couldn't get in. For the heck I gave this test user connect rights on msdb and it worked. The user can get to their DB. I don't remember having to do this :unsure: Anyone know why this might be? We're on SQL 2008 SP2.

    Thanks,

    Ken

  • March 7, 2012 at 10:37 am

    #1456135

    do you have a logon trigger that is sending an email message or logging to a table in MSDB or something?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 7, 2012 at 10:37 am

    #1456136

    Adding a user to the datareader role doesn't necessarily give them access to the database unless CONNECT has been granted at the database level. Permissions are inherited, and depending on how your SQL server is configured SQL-wise, they must be explicitly set:

    Ex:

    use [msdb]

    GO

    GRANT CONNECT TO [DOMAIN\Account]

    GO

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • March 7, 2012 at 1:18 pm

    #1456222

    Adding a user to the datareader role doesn't necessarily give them access to the database unless CONNECT has been granted at the database level. Permissions are inherited, and depending on how your SQL server is configured SQL-wise, they must be explicitly set

    Weird, I'm sure I've done this many times before without explicitly granting connect. Isn't connect implied when adding a user to a DB? Then for sure adding them to db_datareader. What server settings might I look at?

  • March 7, 2012 at 1:25 pm

    #1456224

    hat's the user default database set to and the permissions to it?

    Jared
    CE - Microsoft

  • March 7, 2012 at 1:26 pm

    #1456225

    We do use Resource Governor whose classifier function references a table in master but not msdb.

  • March 7, 2012 at 1:31 pm

    #1456228

    SQLKnowItAll (3/7/2012)

    hat's the user default database set to and the permissions to it?

    Yup, her default DB is the one I'm trying to get her into 😀

  • March 12, 2012 at 3:57 pm

    #1458232

    In case anyone's looking at this, the issue in our environment was that the guest user didn't have connect rights into msdb (not sure why; we may have done this as part of another effort).

    USE MSDB

    GRANT CONNECT TO Guest

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply