Access denied when disabling agent job, despite SqlAgentOperator membership

Question

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)

Answer 1

Please check to see if there is a DENY on the sp_update_job procedure. This may be a direct DENY to your login or it may be a DENY on some group that has your login as a member. DENY always trumps a grant.

If so, then removing your login from the group or having an administrator REVOKE the DENY should solve the problem.

Response prompted by reading Piers7 request. Time has passed, of course.

Answer 2

RLF was right - the user in question was also in the msdb 'TargetServersRole', and that role is explicitly denied permissions on that stored proc. Doh!

Unfortunately fn_my_permissions doesn't show DENYs (it shows the result - no permissions), and the SSMS properties menu (and permissions tab) won't show for system stored procs, or I'd have picked this up days ago.

[@RLF: If you want to promote your comment to an answer, I'll mark it as correct, and remove this one (assuming I can)]

Edit: I should have used this to diagnose - will know for next time:

use msdb
select *, USER_NAME(grantee_principal_id) 
from sys.database_permissions p
where major_id = object_id('dbo.sp_update_job')