Reputation: 149
The database user has been granted SQLAgentOperatorRole
, but there are still error when set job enable/disable. The error message is:
SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'
We set the job enable/disable by sp_update_job
.
Comments:
When executing the following statement, will get the error.
bool result = ObjectInstance.ExecuteStoreQuery<bool>("EXEC usp_prod_SetJobStatus @jobName={0}, @isEnable={1}", jobName, isEnable).FirstOrDefault();
But, while use the following statement, it will execute successful.
ObjectInstance.ExecuteStoreCommand("EXEC msdb.dbo.sp_update_job @job_name={0}, @enabled={1}", jobName, isEnable);
usp_prod_SetJobStatus SP:
CREATE PROCEDURE [dbo].[usp_prod_SetJobStatus]
@jobName VARCHAR(200),
@isEnable BIT
AS
BEGIN
DECLARE @jobId uniqueidentifier
DECLARE @result BIT
SELECT @jobId = job_id FROM msdb.dbo.sysjobs WHERE name = @jobName
IF(@jobId IS NOT NULL)
BEGIN
EXEC @result = msdb.dbo.sp_update_job @job_name=@jobName, @enabled=@isEnable
END
ELSE
BEGIN
SET @result = 1
END
SELECT @result
END
Upvotes: 1
Views: 5901
Reputation: 149
I have solved this problem. The reason is that there is no SELECT permission on sysjobs table for the user. So, we need to grant the SELECT perssion for the user.
USE msdb
GRANT SELECT ON msdb.dbo.sysjobs TO useName
Upvotes: 3
Reputation: 46
You have given agent operator role its having enable and disable but 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 ..You have to execute permissions on sql server agnet .
Upvotes: 0