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'