SQL Server Agent Roles
-
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.
-
Wise words,Hugo.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
-
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
-
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 -
According to BOL (upward from 2005): The SQLAgentReaderRole and the SQLAgentOperatorRole are automatically members of the SQLAgentUserRole.
So it's only a typo.
-
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).
-
SQLAgentAgentRole does not exist.
-
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 -
But that small typo makes the correct answer incorrect.
-
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/ -
Thanks for the question Steve. tks everyone for the dialogue too. very interesting read over my coffee this AM - cheers!
-
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
-
Missed this one because I looked at it too long. Should have followed my first intuition...better luck tomorrow! Thanks for the question.
-
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.
-
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