Execute Job With Stored Proc Signed with Certificate

Question

I'm trying to grant permissions to a user to run one job. I created a certificate, assigned it to the stored proc, and granted the cert role membership to SQLAgentOperatorRole, but when executing the proc the user still gets the error that the job is not found. I can execute the proc with no errors and the job is started. What did I miss?

CREATE PROCEDURE spExecuteRefreshJob
AS
BEGIN

EXEC msdb.dbo.sp_start_job @job_name = 'Refresh Job'

END

CREATE CERTIFICATE ExecuteRestoreJobsCert
   ENCRYPTION BY PASSWORD = 'password'
      WITH SUBJECT = 'Cert for Jobs';
GO

ADD SIGNATURE TO dbo.spExecuteRefreshJob 
   BY CERTIFICATE ExecuteRestoreJobsCert
    WITH PASSWORD = 'password';
GO

CREATE USER ExecuteRestoreJobsUser
   FROM CERTIFICATE ExecuteRestoreJobsCert;
GO

use msdb
EXECUTE sp_addrolemember
@rolename = 'SQLAgentOperatorRole',
@membername = 'ExecuteRestoreJobsUser'

Answer 1

I should have kept researching just a little bit longer. I found this article and discovered I needed to counter-sign a few more stored procedures within msdb. Once I did that my user was able to execute the proc, which kicked off the job.

ADD COUNTER SIGNATURE TO sp_start_job BY CERTIFICATE ExecuteRestoreJobsCert
    WITH PASSWORD = 'password'
ADD COUNTER SIGNATURE TO sp_verify_job_identifiers BY CERTIFICATE ExecuteRestoreJobsCert
    WITH PASSWORD = 'password'
ADD COUNTER SIGNATURE TO sp_sqlagent_notify BY CERTIFICATE ExecuteRestoreJobsCert
    WITH PASSWORD = 'password'
go