SQL minimum permissions to manage any job

Question

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.

Answer 1

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.

Answer 2

Granting job ownership to a domain group would be a wonderful feature - unfortunately that option does not exist.

Rather than granting sa, or db_owner to msdb, you could make this user the job owner of all pertinent jobs and assign the SQLAgentOperatorRole you mentioned. Any sa user would still be able to override this unique permission and alter job ownership in the future.

If you have more than one user requiring these permissions, then my suggestion will not work.