Killing Statistics Jobs

Question

Post reply

Killing Statistics Jobs

Steve Jones - SSC Editor

SSC Guru

Points: 734449
More actions
January 9, 2017 at 8:41 pm

#75758

Comments posted to this topic are about the item Killing Statistics Jobs
January 16, 2017 at 12:10 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 15 total)

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

paul s-306273 SSChampion Points: 10727 More actions · Answer 1

Thought I had this right, but like so many others actually got it wrong.

morlindk SSCrazy Points: 2294 More actions · Answer 2

Permissions

--------------------------------------------------------------------------------

User must have VIEW SERVER STATE permission to access information from the sys.dm_exec_background_job_queue dynamic management view.

KILL STATS JOB permissions default to the members of the sysadmin and processadmin fixed database roles and are not transferable.

...to me this indicates that the sensible thing to do is (i) grant VIEW SERVER STATE plus (ii) add processadmin role.

Adding sysadmin sounds a bit like overkill, but OK, it will do the job, and VIEW SERVER STATE may not be technically necessary, while in practice ...

doug.davidson SSCommitted Points: 1662 More actions · Answer 3

I must be misreading the reference information.

Permissions
User must have VIEW SERVER STATE permission to access information from the sys.dm_exec_background_job_queue dynamic management view.
KILL STATS JOB permissions default to the members of the sysadmin and processadmin fixed database roles and are not transferable.

To me that says, you can be a member of either the sysadmin or the processadmin role, not both are required.

This would mean there are 2 correct answers to this QOTD.

VIEW SERVER STATE + sysadmin

VIEW SERVER STATE + processadmin

Ed Wagner SSC Guru Points: 287024 More actions · Answer 4

I got it wrong as well. I thought it would be process admin + view server state. Of course, sysadmin would do it, but doesn't need anything else. Since the question said to pick 2, I figured sysadmin would have been a gimme and stand alone.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 5

Ditto to processadmin and view server state. sysadmin isn't needed and should be avoided.

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

sknox SSChampion Points: 12388 More actions · Answer 6

Both sysadmin and processadmin roles have VIEW SERVER STATE rights anyway, so that option is redundant. (see https://msdn.microsoft.com/en-us/library/ms188659(v=sql.130).aspx#Permissions of Fixed Server Roles for a nice graphic.)

The question was unclear in that "choose 2" could mean "choose two answers, both of which are required", or "choose two answers, either of which will work".

However, given that VIEW SERVER STATE is redundant to the two other possible correct answers, and none of the other answers make sense, one could reasonably assume that "choose 2" in this case means "choose two answers, either of which will work".

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 7

The question was "... What can I do? (choose 2)."

Not must, or should, but can. You don't need View Server State to kill the jobs. This is required to view what's happening on the instance, but not kill the jobs.

You can grant processadmin, or you can grant sysadmin. Either of those allow the login to kill a job.

Ken SSCoach Points: 17191 More actions · Answer 8

Had a feeling I would get this one wrong. 🙂 It makes perfect sense that view server state would be redundant. Think I need more coffee! Thanks Steve.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

sknox (1/10/2017)
Both sysadmin and processadmin roles have VIEW SERVER STATE rights anyway, so that option is redundant. (see https://msdn.microsoft.com/en-us/library/ms188659(v=sql.130).aspx#Permissions of Fixed Server Roles for a nice graphic.)
The question was unclear in that "choose 2" could mean "choose two answers, both of which are required", or "choose two answers, either of which will work".
However, given that VIEW SERVER STATE is redundant to the two other possible correct answers, and none of the other answers make sense, one could reasonably assume that "choose 2" in this case means "choose two answers, either of which will work".

What CAN you do? The correct answer is start looking for another job because no self respecting DBA would allow anyone but another DBA to do such a thing and that DBA should have the same privs as me. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dstrickrott SSCommitted Points: 1951 More actions · Answer 10

I thought process admin in the choices was a deliberate typo. It's shown as processadmin (no space) in the msdn article

https://msdn.microsoft.com/en-us/library/ms180016.aspx. So I picked sysadmin and VIEW SERVER STATE permission, and was wrong...

Permissions

--------------------------------------------------------------------------------

User must have VIEW SERVER STATE permission to access information from the sys.dm_exec_background_job_queue dynamic management view.

KILL STATS JOB permissions default to the members of the sysadmin and processadmin fixed database roles and are not transferable.

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 11

dstrickrott - Tuesday, January 10, 2017 3:53 PM
I thought process admin in the choices was a deliberate typo. It's shown as processadmin (no space) in the msdn article https://msdn.microsoft.com/en-us/library/ms180016.aspx. So I picked sysadmin and VIEW SERVER STATE permission, and was wrong...Permissions--------------------------------------------------------------------------------User must have VIEW SERVER STATE permission to access information from the sys.dm_exec_background_job_queue dynamic management view.KILL STATS JOB permissions default to the members of the sysadmin and processadmin fixed database roles and are not transferable.

No, apologies for that. Typo fixed. This role is one word.

dstrickrott SSCommitted Points: 1951 More actions · Answer 12

Steve Jones - SSC Editor - Thursday, January 12, 2017 11:09 AM
dstrickrott - Tuesday, January 10, 2017 3:53 PM
I thought process admin in the choices was a deliberate typo. It's shown as processadmin (no space) in the msdn article https://msdn.microsoft.com/en-us/library/ms180016.aspx. So I picked sysadmin and VIEW SERVER STATE permission, and was wrong...Permissions--------------------------------------------------------------------------------User must have VIEW SERVER STATE permission to access information from the sys.dm_exec_background_job_queue dynamic management view.KILL STATS JOB permissions default to the members of the sysadmin and processadmin fixed database roles and are not transferable.
No, apologies for that. Typo fixed. This role is one word.

Thanks for the correction Steve.

TomThomson SSC Guru Points: 104773 More actions · Answer 13

Steve Jones - SSC Editor - Tuesday, January 10, 2017 9:52 AM
The question was "... What can I do? (choose 2)."Not must, or should, but can. You don't need View Server State to kill the jobs. This is required to view what's happening on the instance, but not kill the jobs.You can grant processadmin, or you can grant sysadmin. Either of those allow the login to kill a job.

yes, fine, but some people read the question as asking to name two things such that if you do both of them you'll get the desired result. I definitely CAN get someone the ability to use KILL STATS JOB by both giving them VIEW SERVER STATE permission and making them a member of the processadmin role, and specifically because VIEW SERVER STATE is included in processadmin that is the best way to do it if you are going to do two things because you are not granting too much as you would be if instead of granting that permission you granted something that wasn't included in the processadmin role. So I think the qestion would have been much better if it had had clear and unambiguous wording, not just "what can I do".

Besides, isn't it about 5 years now since Microsoft told us we should stop using fixed server roles and start granting more specific permissions? And even without that advice I would regard someone who handed out sysadmin privilege just to enable someone to delete stats jobs as a positive danger to security.

Tom