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

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.