Post reply

SQL Server Agent Roles

  • March 22, 2012 at 5:02 am

    #1462616

    Hugo Kornelis

    When I submit a question (and I have done so multiple times already - 22 times, to be exact), I go through great lengths to prevent any errors or ambiguities. But errors still slip through

    +1

    I do not know the author of the saying, "To err is human" , but it is a truth. Like you I have submitted and have had published 58, with 3 submitted and scheduled in the coming days. In some cases I have been beaten from pillar to post (as the saying goes). What I hope is those who could post a good QOD do NOT hold back, because of some of the more adverse comments posted in the discussions of the QOD.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • March 22, 2012 at 5:07 am

    #1462618

    Wise words,Hugo.

    “When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

  • March 22, 2012 at 5:19 am

    #1462619

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

    Interesting. I don't trust BoL to be right 100% of the time, nor SSMS (for example both claim that many things in master with names beginning sp_ which don't get found in preference to local things with the same name are system stored procedures, while clearly they are nothing of the sort).

    I have a very clear recollection that membership is not transitive so I decided to test it. Here are four lines of code which appear to answer the question:

    use msdb

    declare @tab table(DbRole sysname, MemberName sysname, MemberSID varbinary(85))

    insert @tab exec sp_helprolemember

    select * from @tab where DbRole like 'SQLAg%' and MemberName like 'SQLAg%'

    (It's a pity sp_helprolemember is an SP instead of a TVF, so that there could be just 1 line of code for that.)

    You'll see that the result is in accord with what I described, not in accord with today's question and answer.

    So which to believe: SSMS and BoL, or what the database tells me when I query it? I prefer the latter.

    But of course maybe sp_helprolemember doesn't do what BoL says it does but only gives a subset of the members.

    Tom

  • March 22, 2012 at 5:30 am

    #1462623

    Wow, Steve, always interesting. After reading all the responses, I'm tempted to jump in. But, I'm in a good mood this morning, and I did correctly parse the intent in the answers, so I'll just say: Thanks for the question Steve.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • March 22, 2012 at 5:40 am

    #1462626

    According to BOL (upward from 2005): The SQLAgentReaderRole and the SQLAgentOperatorRole are automatically members of the SQLAgentUserRole.

    So it's only a typo.

  • March 22, 2012 at 6:53 am

    #1462667

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

    Interesting. I don't trust BoL to be right 100% of the time, nor SSMS (for example both claim that many things in master with names beginning sp_ which don't get found in preference to local things with the same name are system stored procedures, while clearly they are nothing of the sort).

    I have a very clear recollection that membership is not transitive so I decided to test it. Here are four lines of code which appear to answer the question:

    use msdb

    declare @tab table(DbRole sysname, MemberName sysname, MemberSID varbinary(85))

    insert @tab exec sp_helprolemember

    select * from @tab where DbRole like 'SQLAg%' and MemberName like 'SQLAg%'

    (It's a pity sp_helprolemember is an SP instead of a TVF, so that there could be just 1 line of code for that.)

    You'll see that the result is in accord with what I described, not in accord with today's question and answer.

    So which to believe: SSMS and BoL, or what the database tells me when I query it? I prefer the latter.

    But of course maybe sp_helprolemember doesn't do what BoL says it does but only gives a subset of the members.

    Tom,

    That stored procedure simply executes something like the following (using profiler)

    select m.*, g.name, u.name,g.*, u.*

    from sys.database_role_members m inner join

    sys.database_principals u on m.member_principal_id = u.principal_id inner join

    sys.database_principals g on m.role_principal_id = g.principal_id

    where u.name like 'SQLAgent%' or g.name like 'SQLAgent%'

    As you can see, the DB thinks that the SQLAgentReaderRole is a member of SQLAgentUserRole. And the SQLAgentOperatorRole is a member of SQLAgentReaderRole. So Tom is correct in saying that the DB shows something different than BOL.

    How ever, Tom is then incorrect about this QOTD. Due to Role inheritence, SQLAgentOperatorRole IS a member of SQLAgentUserRole by default since the SQLAgentReaderRole is a member. (Role Based Security principles).

  • March 22, 2012 at 7:11 am

    #1462682

    SQLAgentAgentRole does not exist.

  • March 22, 2012 at 7:13 am

    #1462683

    straight forward except the small typo.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • March 22, 2012 at 7:15 am

    #1462686

    But that small typo makes the correct answer incorrect.

  • March 22, 2012 at 7:30 am

    #1462700

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

    So which to believe: SSMS and BoL, or what the database tells me when I query it?

    I think you can believe both.

    SSMS and BOL give you the functional answer. When I work with roles, I am interested in the permissions I get. Functionally, it is irrelevant if this is achieved by direct role membership or by inherited role membership. So it makes sense for both BOL and SSMS to make no distinction.

    Your query tells you how this is implemented. Using inheritance makes it possible to implement the intended role memberships by just two sp_addrolemember calls (or whatever equivalent Microsoft uses for fixed roles).

    I 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.

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • March 22, 2012 at 9:07 am

    #1462794

    Thanks for the question Steve. tks everyone for the dialogue too. very interesting read over my coffee this AM - cheers!

  • March 22, 2012 at 9:10 am

    #1462799

    I have to add my own gripe here about this typo. I took "SQLAgentAgent" to be a trick and not a typo so I didn't pick it.

    Cheers

  • March 22, 2012 at 9:49 am

    #1462839

    Missed this one because I looked at it too long. Should have followed my first intuition...better luck tomorrow! Thanks for the question.

  • March 22, 2012 at 12:18 pm

    #1462952

    Thanks for the interesting question. Despite the typo, elimination on analyzing all possible answers leads to a single option.

    Regarding the typo, besides Hugo's wise words, "to err is human", and the community has repeatedly asked submitters to make sure there are no errors in their submissions, but nobody's perfect.

    "The only persons that don't make a mistake are those who don't do a thing"

    "El" Jerry.

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

    To better understand your help request, please follow these best practices.[/url]

  • March 22, 2012 at 12:47 pm

    #1462966

    Guessed right considering the typo.

Viewing 15 posts - 16 through 30 (of 36 total)

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