3

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'
  • Do you need to grant Execute on spExecuteRefreshJob to ExecuteRestoreJobsUser? – SqlWorldWide Jun 1 '17 at 20:33
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.