patternsqlMinor
Execute Job With Stored Proc Signed with Certificate
Viewed 0 times
storedcertificatewithprocsignedjobexecute
Problem
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'Solution
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'
goCode Snippets
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'
goContext
StackExchange Database Administrators Q#175211, answer score: 3
Revisions (0)
No revisions yet.