SQL Server – Minimum Permissions to Manage Jobs

sql server

I am looking for a way to grant minimum rights to a login to manage ANY SQL Server Agent job. The SQLAgentOperatorRole allows to manage own jobs and disable/enable any job, however I need to allow the user to edit any job on the instance. Is there a built-in msdb role that I am not seeing. Obviously I dont want to grant the user SA rights on the instance and would like to avoid granting owner rights to MSDB.

Any advice is appreciated.

Best Answer

Unfortunately, the SQLAgentOperatorRole msdb role is the most privileged role for "managing" only jobs on a SQL server, and it only gives the users the ability to disable/enable jobs/schedules not owned by the user.

The only supported way for giving full access to all jobs AND manage them using SQL Server Management Studio is to add the users to the sysadmin server role.

The reason for this is the way the sp_update_job* procedures are written, they are actually checking role membership for access. Excerpt from sp_update_job is below. Note the comments and the explicit check for @x_owner_sid <> SUSER_SID() along with NOT being in sysadmin.

  -- Check permissions beyond what's checked by the sysjobs_view
  -- SQLAgentReader and SQLAgentOperator roles that can see all jobs
  -- cannot modify jobs they do not own
  IF ( (@x_owner_sid <> SUSER_SID())                  -- does not own the job
      AND (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)   -- is not sysadmin
      AND (@enable_only_used <> 1 OR ISNULL(IS_MEMBER(N'SQLAgentOperatorRole'), 0) <> 1))
  BEGIN
   RAISERROR(14525, -1, -1);
   RETURN(1) -- Failure
  END

  -- Check job category, only sysadmin can modify mutli-server jobs        
  IF (EXISTS (SELECT * FROM msdb.dbo.syscategories WHERE (category_class = 1) -- Job
                                                     AND (category_type = 2) -- Multi-Server
                                                     AND (category_id = @x_category_id)
                                                     AND (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1))) -- is not sysadmin
  BEGIN
     RAISERROR(14396, -1, -1);
     RETURN(1) -- Failure
  END

One alternative/workaround if you can live without SSMS management, is to utilize certificates for access to the underlying sp_update_job* procs, and then create wrapper procedures (which is why you cannot use SSMS still). For a full example, see Uwe Ricken db Berater GmbH response in this MSDN post.

Related Question