SQL Server Agent Roles
-
Comments posted to this topic are about the item SQL Server Agent Roles
-
Got this right, on the assumption 'SQLAgentAgentRole' was meant to be 'SQLAgentReaderRole', as none of the other answers were correct, and 'SQLAgentAgentRole' doesn't seem to exist.
-
Indeed, small typo in the correct answer.
However, very nice question, thanks Steve.
(you read that MSDN page one day and you thought: "I shall make 5 QotD of this!"? :-D)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
The typo costed me for real,what is 'SQLAgentAgentRole' thought that was a trick any the only meaningful and true answer was the first one,although it did include all the three roles.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
-
Hi,
I got ot right, but should replace "SQLAgentAgentRole" with "SQLAgentReaderRole"
Thanks.
-
kapfundestanley (3/22/2012)
The typo costed me for real,what is 'SQLAgentAgentRole' thought that was a trick any the only meaningful and true answer was the first one,although it did include all the three roles.But the first answer ISN'T true--it's reversed; the SQLAgentReaderRole is a member of the SQLAgentUserRole, given the terminology used here. I don't agree with that terminology, mind you, because it would be far too easy for a newbie to confuse logins that are members of a role with this nebulous idea of roles being members of other roles that the question introduces. I suspect this is why the linked article never uses this terminology!
-
paul.knibbs (3/22/2012)
kapfundestanley (3/22/2012)
The typo costed me for real,what is 'SQLAgentAgentRole' thought that was a trick any the only meaningful and true answer was the first one,although it did include all the three roles.But the first answer ISN'T true--it's reversed; the SQLAgentReaderRole is a member of the SQLAgentUserRole, given the terminology used here. I don't agree with that terminology, mind you, because it would be far too easy for a newbie to confuse logins that are members of a role with this nebulous idea of roles being members of other roles that the question introduces. I suspect this is why the linked article never uses this terminology!
Indeed, with simple elimination the correct answer could be deduced. Even if there's a typo in it.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Thanks for the question. I think even with a small typo the answer was relatively straightforward. Three right in a row - I must be on a roll. :w00t:
-
Thank you for the easy and straight forward question....
-
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
You never know: reading my book: "All about your computer" might just tell you something you never knew!
lulu.com/kaspencer -
kaspencer (3/22/2012)
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.
Wow, you sure come across as very angry right now. Maybe take a deep breathe and count to ten first?
First: Having someone (or a panel) proofread questions has been suggested before. Steve always replied that he has tried this once. After a good start, people started missing deadlines. Nothing you can blame them for; job and family should always take precedence over volunteering. But it did give Steven problems; that's why he stopped doing it.
Second: Sacking question authors will be hard. They are not employed by SQLServerCentral. This QotD is a community thing - everyone can submit questions, either by clicking the "Write For Us" link on the left hand side of the screen, or by clicking "Contribute a question of your own" on the bottom of the question's results screen.
The only question author who is emplyed by SQLServerCentral is Steve himself, who will fill in questions if there are not sufficient coming from the community. Today's question was by him, but I don't think he'll fire himself.
Third: Making mistakes is human. We all do that. If you only recommend people to your clients who never make any mistakes, you'll have very little people left to recommend. (On the other hand, the up side is that if you do find someone who is that perfect, you wouldn't need to test his or her code, so that would save you a lot of time!)
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; I estimate that there were problems with at least half of my questions. Some serious (typos in the question or answer that affected the answer, relevant details left out), some minor (convoluted interpretations that, while not actually ruled out, were not really relevant; minor typos that did not affect the correct answer). Perfection is a lot harder to achieve than those who never tried it tend to think!
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/ -
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. The only explanation I can think of fo this answer is that whoever posed it doesn't understand the difference between "A is a member of B" and "A inherits permissions from B through its membership of C", and believes falsely that role membership is a transitive relationship - certainly the explanation suggests that.
The SQL version I know and understand (SQL 2008 R2) have the following relationships:-
1) SQLAgentOperatorRole is a member of SQLAgentReaderRole
2) SQLAgentReaderRole is a member of SQLAgentUserRole
and there are no other membership relationships between them (although SQLAgentOperatorRole inherits permissions from SQLAgentUserRole through its membership of SQLAgentReaderRole, it is not a member of SQLAgentUserRole).
So exactly none of the four options for the answer is correct, since none of them shows two dietinct roles each with exactly one the others as member.
Maybe Steve can fix this when he gets round to fixing the SQLAgenAgenRole misprint; and award points back to everyone who answered anything at all, since providing a wrong answer was the only way to find out what on earth was going on.
Tom
-
Koen Verbeeck (3/22/2012)
Indeed, small typo in the correct answer.However, very nice question, thanks Steve.
(you read that MSDN page one day and you thought: "I shall make 5 QotD of this!"? :-D)
Not a small typo, it's completely wrong quite apart from the typo! See my earlier post.
Tom
-
For me it was guess what the question was meant to say or get no points anyway.
Shouldn't have guessed really...
No pints today.
-
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).
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/
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply