I am attempting to disable a SQL agent job from an account which a member of the SqlAgentOperator role (but not sysadmin)
The doco says this should be possible
3 SQLAgentOperatorRole members can enable or disable local jobs they do not own by using the stored procedure sp_update_job and specifying values for the @enabled and the @job_id (or @job_name) parameters. If a member of this role specifies any other parameters for this stored procedure, execution of the procedure will fail.
...and indeed the code for msdb.dbo.sp_update_job appears to support this - it has explicit handling for exactly this scenario. Yet if I run:
use [MSDB] go select suser_sname() if(is_member('SqlAgentOperatorRole')<>1) raiserror('Not in required role',16,1) go exec dbo.sp_update_job @job_name='a job', @enabled=0
... I get the following error:
Msg 229, Level 14, State 5, Procedure sp_update_job, Line 1 The EXECUTE permission was denied on the object 'sp_update_job', database 'msdb', schema 'dbo'.
The error message appears to indicate the proc's not even being run, but even granting that user explicit EXECUTE on that stored proc doesn't seem to fix it.
Does anyone know how to grant a user the ability to disable/enable SQL agent jobs that they don't own, or has successfully used this functionality. Maybe it's just broken in SQL 2012 SP1 CU4 (which is what I am using)