Allow Windows AD group to own a SQL job

Question

The SQL Agent Jobs sits above the user level and requires a login to be assigned to the owner. But it doesn't take a group login as an accepted parameter. I need to use the Windows AD group as owner because I have different SQL users and some of them should see only the specific jobs. As now Ive created separate jobs for every user using SQLAgentUserRole which is not good for sure and the database is full of 1:1 jobs, each of them with different owner to avoid seeing the other jobs.

The whole picture: Lets say that I have 10 different jobs in the database. One of those jobs is named UserJob. I want specific users when connecting to the database and expand the jobs section to see ONLY the job named "UserJob" and be able to start it. I dont need it via Stored procedure, etc. I just need to start the job via the SSMS (right click, start job, enter parameters if needed). Thanks.

Answer 1

I use the following modification to the sysjobs_view. It allows the use of any msdb custom role names. The corresponding Agent Category name must be an exact match of the role name. It then functions just like the solution Alex provided, just with multiple roles/categories possible.

ALTER VIEW sysjobs_view  
AS  
SELECT jobs.job_id,  
       svr.originating_server,  
       jobs.name,  
       jobs.enabled,  
       jobs.description,  
       jobs.start_step_id,  
       jobs.category_id,  
       jobs.owner_sid,  
       jobs.notify_level_eventlog,  
       jobs.notify_level_email,  
       jobs.notify_level_netsend,  
       jobs.notify_level_page,  
       jobs.notify_email_operator_id,  
       jobs.notify_netsend_operator_id,  
       jobs.notify_page_operator_id,  
       jobs.delete_level,  
       jobs.date_created,  
       jobs.date_modified,  
       jobs.version_number,  
       jobs.originating_server_id,  
       svr.master_server  
FROM msdb.dbo.sysjobs as jobs  
  JOIN msdb.dbo.sysoriginatingservers_view as svr  
    ON jobs.originating_server_id = svr.originating_server_id  
  --LEFT JOIN msdb.dbo.sysjobservers js ON jobs.job_id = js.job_id  
  JOIN msdb.dbo.syscategories AS cats ON (jobs.category_id=cats.category_id)
WHERE (owner_sid = SUSER_SID())  
   OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)  
   OR (ISNULL(IS_MEMBER(N'SQLAgentReaderRole'), 0) = 1)  
   OR ( (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1) AND  
        (EXISTS(SELECT * FROM msdb.dbo.sysjobservers js   
         WHERE js.server_id <> 0 AND js.job_id = jobs.job_id))) -- filter out local jobs    
   OR (ISNULL(IS_MEMBER(cats.name), 0) =1)
GO

I also have a complementary stored procedure in msdb, which allows any user in the same role/category to take ownership of a job.

CREATE OR ALTER PROCEDURE [dbo].[JobTakeOwnership]
    @JobName nvarchar(128) = NULL
WITH EXECUTE AS OWNER
AS
/*********************************************************
This procedure is used to change the owner of a SQL Agent Job, to the account executing this stored procedure.
The account executing this stored procedure must be a member of the category the job belongs to.
---------------------------------
Parameter Definition:
---------------------------------
@JobName = 'job_name'
Name of the SQL Agent Job you wish to take ownership of.
*********************************************************/
SET NOCOUNT ON;

BEGIN
  declare @jobexistsforuser as int
  declare @jobcatmember as int
  declare @currentjobid as uniqueidentifier
  declare @jobcatname as nvarchar(128)
  declare @newownersid as varbinary(85)
  declare @oldownersid as varbinary(85)
  declare @jobownerbefore as sysname
  declare @jobownerafter as sysname

  select @oldownersid=owner_sid, @currentjobid=job_id
    from sysjobs
    where name=@Jobname

  EXECUTE AS CALLER
  select @jobexistsforuser=count(*)
    from sysjobs_view
    where job_id = @currentjobid

  select @jobcatname=b.name
    from sysjobs_view a join syscategories b
    on a.category_id=b.category_id
    where job_id = @currentjobid

  set @newownersid = suser_sid()
  set @jobcatmember=IS_MEMBER(@jobcatname)

  /* allow sysadmins to run this SP and override the job category check */
  if is_srvrolemember('sysadmin')=1 set @jobcatmember=1
  REVERT

  if @currentjobid is NULL
    print 'Job "'+@JobName+'" does not exist.'
  else if @jobexistsforuser=0
    print 'You do not have permission to view or modify this job.'
  else if @newownersid = @oldownersid
    print 'You already own this job, no change required.'
  else if @jobcatmember is NULL  /* 3 possibilities, NULL, 0, 1 */
    print 'This job is not pre-configured for transferring ownership.'
  else if @jobcatmember=0
    print 'You are not a member of the "'+@jobcatname+'" group.'
  else
    begin
      select @jobownerbefore=suser_sname(owner_sid) from sysjobs where name=@Jobname
      print 'Job owner before update: ' +@jobownerbefore

      update sysjobs set owner_sid      = @newownersid
                      , date_modified  = CURRENT_TIMESTAMP
                      , version_number = version_number + 1
                    where job_id = @currentjobid
                    
      /*the owner of job schedules also need be changed, 
      otherwise the new job owner will not be able to modify the schedules of the job.
      Modified by Dicheng Liu on 2012-07-06*/                
      update sysschedules 
      set owner_sid      = @newownersid
        , date_modified  = CURRENT_TIMESTAMP
        , version_number = version_number + 1
      where schedule_id in (select schedule_id from sysjobschedules 
                            where job_id =@currentjobid)

      select @jobownerafter=suser_sname(owner_sid) from sysjobs where name=@Jobname
      print 'Job owner after update: ' +@jobownerafter


      /* SQL agent caches job attributes - force the SQL agent to update cached information */
      print ''
      print 'Refreshing SQL Agent cache for job: ' + @JobName
      EXECUTE AS CALLER
        exec sp_sqlagent_notify @op_type     = N'J'
                              , @job_id      = @currentjobid
                              , @action_type = N'U'
      REVERT
    end
END
GO

GRANT EXECUTE ON [dbo].[JobTakeOwnership] TO [SQLAgentUserRole]
GO

Answer 2

As per the docs SSMS checks user membership in the following Database Roles to show SQL Server Agent tree node:

• SQLAgentUserRole
• SQLAgentReaderRole
• SQLAgentOperatorRole

I used SQL Server Profiler to find what queries are executed when you first connect to database in Object Browser and expand various nodes.

For SQL Server Agent it uses SELECT * FROM msdb.dbo.sysjobs_view view to list Jobs. This view can be modified.

Changes

1. Create a new Database Role in msdb database. I called it "CustomJobRole".
2. I then created a new Job (I assume you already have a Job) called "TestJob"
3. Create a low privilege user that should be able to see and run only "TestJob".
4. Add this user to "CustomJobRole" and "SQLAgentReaderRole" and/or "SQLAgentOperatorRole" (see linked above docs for details)
5. Modify sysjobs_view as follows:

(see comments in code)

ALTER VIEW sysjobs_view
AS
SELECT jobs.job_id,
       svr.originating_server,
       jobs.name,
       jobs.enabled,
       jobs.description,
       jobs.start_step_id,
       jobs.category_id,
       jobs.owner_sid,
       jobs.notify_level_eventlog,
       jobs.notify_level_email,
       jobs.notify_level_netsend,
       jobs.notify_level_page,
       jobs.notify_email_operator_id,
       jobs.notify_netsend_operator_id,
       jobs.notify_page_operator_id,
       jobs.delete_level,
       jobs.date_created,
       jobs.date_modified,
       jobs.version_number,
       jobs.originating_server_id,
       svr.master_server
FROM msdb.dbo.sysjobs as jobs
  JOIN msdb.dbo.sysoriginatingservers_view as svr
    ON jobs.originating_server_id = svr.originating_server_id
  --LEFT JOIN msdb.dbo.sysjobservers js ON jobs.job_id = js.job_id
WHERE 
    -- Custom: Add Condition for your Custom Role and Job Name
    ( (ISNULL(IS_MEMBER(N'CustomJobRole'), 0) = 1) AND jobs.name = 'TestJob' )
    OR (owner_sid = SUSER_SID())
   OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
   -- Custom: In order for users to be able to see and start Jobs they have to be members of SQLAgentReaderRole/SQLAgentOperatorRole
   -- but these roles gives them ability to see all jobs so add an exclusion
   OR ( ISNULL(IS_MEMBER(N'SQLAgentReaderRole'), 0) = 1 AND ISNULL( IS_MEMBER(N'CustomJobRole'), 0 ) = 0 ) 
   OR ( (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1) AND
        (EXISTS(SELECT * FROM msdb.dbo.sysjobservers js 
         WHERE js.server_id <> 0 AND js.job_id = jobs.job_id))) -- filter out local jobs

Note: commented out LEFT JOIN is original code and has nothing to do with the solution.

Summary

This method is "hacky" as it only modifies the job list for certain users and does not actually prevent them from running other jobs via code, in other words this does not offer any security, just convenience of clean UI. Implementation is simple but, obviously not scalable: Job name is hard-coded and negative membership presence is used (i.e. AND ISNULL( IS_MEMBER(N'CustomJobRole'), 0 ) = 0). IMO, it is the simplest and most reliable (least side effects) method though.

Tested on

SSMS v18.9.2 + SQL Server 2014 SP3

Editing Job Step Workaround

It is not possible to modify Job Step unless you are a Job Owner or a Sysadmin. One, even more "hacky", way to work around this problem is to create a table that would hold all input parameters and give users insert/update access to this table. Your SP can then read parameters from this table. It should be easy for Users to Right-Click -> Edit on the table and modify data.

For the table structure I would recommend the following:

• Assuming you have relatively few parameters I suggest that you create one column per parameter. This way you have correct data types for each parameter.
• Add an After Insert / Delete trigger to the table to ensure that the table always has exactly one row of data.