title: Permissions to set job Enable/Disable in SQL Server
body: <p>The database user has been granted <code>SQLAgentOperatorRole</code>, but there are still error when set job enable/disable. The error message is:</p>
  
  <blockquote>
    <p>SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'</p>
  </blockquote>
  
  <p>We set the job enable/disable by <code>sp_update_job</code>.</p>
  
  <p>Comments: </p>
  
  <p>When executing the following statement, will get the error. </p>
  
  <pre><code>bool result = ObjectInstance.ExecuteStoreQuery&lt;bool&gt;("EXEC usp_prod_SetJobStatus @jobName={0}, @isEnable={1}", jobName, isEnable).FirstOrDefault();
  </code></pre>
  
  <p>But, while use the following statement, it will execute successful.</p>
  
  <pre><code> ObjectInstance.ExecuteStoreCommand("EXEC msdb.dbo.sp_update_job @job_name={0}, @enabled={1}", jobName, isEnable);
  </code></pre>
  
  <p>usp_prod_SetJobStatus SP:</p>
  
  <pre><code>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
  </code></pre>