1

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.

migrated from stackoverflow.com Mar 18 '16 at 16:06

This question came from our site for professional and enthusiast programmers.

  • How are you expecting users to manage the jobs? With SSMS or with a custom GUI/application? I'm asking because the answer depends on what you're planning to do. – spaghettidba Mar 18 '16 at 16:15
  • A proxy account would do the trick: msdn.microsoft.com/en-us/library/ms187901.aspx – Peter Mar 18 '16 at 16:52
  • @spaghettidba The user with manage the jobs via SSMS. – fed1999 Mar 18 '16 at 18:48
  • @fed1999 then I'm afraid that granting sysadmin is the only option you have, besides giving job ownership to the users. See the two answers you already have here – spaghettidba Mar 19 '16 at 8:18
2

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.