Post reply

SQL Server Agent Roles

  • March 22, 2012 at 2:51 pm

    #1463031

    Hugo Kornelis (3/22/2012)

    will give you that there is some level of ambiguity in the question. I read the question as being about functional (i.e. direct or indirect) role membership; judging by the comments so far and the answer distribution, that is how most people read it. You apparently read the question as being about direct role membership only; for that interpretation, none of the answer options is correct.

    Yes, I think that's right. I was thinking about structure of the (catalog) schema, not about what function it's designed to achieve, and in fact it's obvious that what people using the system need is the information about the function. (It was Venoym's comment that woke me up to this, but your comment expressed it more clearly, using the terms "functional" and "direct"). So looking at the structure was unhelpful, hence I was wrong.

    Tom

  • March 22, 2012 at 7:16 pm

    #1463128

    Hugo Kornelis (3/22/2012)

    L' Eomot Inversé (3/22/2012)

    I don't know which version of SQL Server this is supposed to apply to. I don't thionk there is any version it actually does apply to.

    Sorry, Tom, but this is absolutely not true.

    First: Follow the link provided in the answer. You'll see a blue box with a "Security Note", that contains this text:

    "The SQLAgentReaderRole and the SQLAgentOperatorRole are automatically members of the SQLAgentUserRole."

    Second: Open SSMS, use Object Explorer to go to Databases, System Databases, msdb, Security, Roles, Database Roles; then double-click SQLAgentUserRole. You'll see a screen that lists SQLAgentOperatorRole and SQLAgentReaderRole as members of this role. (See image - screenshot taken on SQL Server 2008R2).

    So the GUI dialog shows "nesting" or merely "permissions inheritance"? Because I thought Operator was merely a member of Reader which is a member of User, thereby inheriting those permissions. When I run the member query, Operator is a member of Reader only. It does not have the property of role member of User. So the membership nesting must be parsed to determine permissions each time access is required? Or are permissions cached per procedure or per connection? Or...?

    If Active Directory would report nested memberships like this, it sure would simplify permissions troubleshooting issues... or would it???

    Great question. Made me think more about what is happening under the covers. Yet another topic to dig deeper on 🙂

    Peter Trast
    Microsoft Certified ...(insert many literal strings here)
    Microsoft Design Architect with Alexander Open Systems

  • March 22, 2012 at 10:15 pm

    #1463153

    Though typo error, it was a great question... Thanks.

    Thanks

  • March 23, 2012 at 9:41 am

    #1463500

    My apologies for the question. Yesterday was a holiday for me, so I'm getting back today.

    The typo is corrected, and thanks to all who pointed it out. Points have been awarded back for that reason.

  • March 23, 2012 at 9:47 am

    #1463506

    kaspencer (3/22/2012)

    Why is the number of errors in Questions of the Day so great?

    How are we supposed to interpret typographical errors?

    I have yet to build the stored procedure SP_ESP so I simply did not treat SQLAgentAgentRole as a typographical error - I treated it as a distractor.

    A message to Steve Jones: Steve, appoint a named reader of every QotD before questions are used on the site. Otherwise, SACK those question-writers who do not read their submissions properly.

    One thing is for sure - all those submitters of duff questions will not be recommended to my clients - no doubt their coding is as error-prone as their questions.

    Kenneth Spencer

    I think I'll write this off to a bad day. It seems like a fairly extreme response to what is a low impact issue. We correct issues as soon as we can, but it isn't necessarily the first minute someone posts.

    Since I do almost all the post question corrects, and I track the date in a quiz, it's been a month since there was a correction. I would guess that there were 4 or 5 this year, across 60 days or so, which may be a little high.

    If you think it's easy, I would encourage you to try submitting one. It becomes tricky to actually word a question well, test it, and then ensure in your cut/paste or typing to actually not make a mistake. This typo, was just that, and it's one that becomes easy to miss, even with multiple people reviewing it.

    This is definitely my fault, and I'm not making excuses. I could have cut and pasted the role name into the form, but that's not something I normally do. I typed it, reread it as SQLAgentReader and let it go.

    Ultimately this is a tickler tool to let you hear about different features in SQL Server, maybe learn a bit more, but it's not intended to prove or test knowledge. If you feel it doesn't work for you, you are free to not participate.

  • March 23, 2012 at 12:47 pm

    #1463695

    Great and interestingly tricky question on the SQL Server Agent Roles. Basic and very important to understand. Thanks.

  • April 2, 2012 at 7:08 pm

    #1467989

    Good question. Thanks for submitting.

    http://brittcluff.blogspot.com/

Viewing 7 posts - 31 through 36 (of 36 total)

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