SQL Agent Jobs – Permissions

SQLAgentReaderRole can solve the problems of giving rights to the users for administration of SQL Agent jobs. It is located in MSSQL Database and is a Database role. With the help of this it is possible to assign the user the permissions to see any SQL Agent job in the database server. However, this can also create some problems as the users will be able to see the jobs which are owned by other users. It is also possible for the users to check the history of the job. But the group of users will not be able to execute the job and you will have to add the user to SQLAgentReaderRole as a member. Follow the steps given below to do so :

Enter the following commands

use msdb

EXECUTE sp_addrolemember

@rolename = ‘SQLAgentReaderRole’,

@membername = ‘username’

There are 2 more SQL Agents role which are available in SQL Server 2005.

1. SQLAgentUserRole – This will allow the users to manage the jobs after they have created the job.
2. SQLAgentOperatorRole – The permissions assigned to SQLAgentUserRole are also assigned to SQLAgentOperatorRole. However, SQLAgentOperatorRole also provides permissions to the users to start the local jobs that they do not own.
   Custom Code for viewing jobsIf you do not want to assign SQLAgentReaderRole then you have an option. Even if you do not have  SQL server 2005 then too you do not have to worry as there is an option. Below is the listing of jobs on the system with the custom system and also the details of the specific jobs. You can assign permissions to the users to view the jobs on the system according to you after you have created the procedure given below.

   use master

   go

   CREATE PROCEDURE [dbo].[sp_ViewJobListing]

   (

   @JobName VARCHAR(255)=NULL

   )

   AS

   BEGIN

   IF OBJECT_ID(‘tempdb..#Results’)>0

   DROP TABLE #Results

   CREATE TABLE #Results

   (

   job_id UNIQUEIDENTIFIER NOT NULL,

   last_run_date INT              NOT NULL,

   last_run_time INT              NOT NULL,

   next_run_date INT              NOT NULL,

   next_run_time INT              NOT NULL,

   next_run_schedule_id INT              NOT NULL,

   requested_to_run INT              NOT NULL,

   request_source INT              NOT NULL,

   request_source_id SYSNAME   COLLATE DATABASE_DEFAULT NULL,

   running  INT              NOT NULL,

   current_step INT              NOT NULL,

   current_retry_attempt  INT              NOT NULL,

   job_state  INT              NOT NULL

   )

   DECLARE @JobID VARCHAR(100)

   SELECT TOP 1 @JobID = job_ID FROM msdb.dbo.sysjobs

   INSERT INTO #Results

   EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @JobID

   SELECT

   s.Name,

   CASE WHEN s.enabled = 0 THEN ‘No’ ELSE ‘Yes’ END AS Enabled,

   CASE WHEN next_run_date > 0 THEN ‘Yes’ ELSE ‘No’ END AS Scheduled,

   sc.name AS Category,

   current_step AS CurrentExecutionStep,

   last_run_date,

   next_run_date,

   CASE WHEN xp.running = 0 THEN ‘Not Running’ ELSE ‘Executing…’ END AS Status,

   ISNULL((

   SELECT CASE WHEN run_status = 1 THEN ‘Succeeded’ WHEN run_status = 3 THEN ‘Cancelled’ WHEN run_status = 0 THEN ‘Failed’ WHEN run_status IS NULL THEN ‘Unknown’ END AS LastRunStatus

   FROM

   msdb..sysjobhistory sho

   WHERE

   sho.job_id = xp.job_id AND

   sho.instance_id =

   (

   SELECT MAX(instance_id)

   FROM msdb..sysjobhistory sj (NOLOCK)

   WHERE sj.job_id = sho.job_id

   )

   ) ,’Unknown’) AS LastRunStatus

   FROM     #Results xp

   INNER JOIN msdb..sysjobs s on xp.job_id = s.job_id

   INNER JOIN msdb..syscategories sc on s.category_id = sc.category_id

   WHERE

   s.Name = ISNULL(@JobName, s.Name)

   ORDER BY s.Name

   IF @JobName IS NOT NULL

   BEGIN

   CREATE TABLE #JobHistory

   (

   StepID INT,

   StepName SYSNAME,

   Message NVARCHAR(1024),

   RunStatus INT,

   RunDate INT,

   RunTime INT,

   RunDuration INT,

   operator_emailed NVARCHAR(20),

   operator_netsent NVARCHAR(20),

   operator_paged NVARCHAR(20)

   )

   INSERT INTO #JobHistory

   SELECT

   sjh.step_id,

   sjh.step_name,

   sjh.message,

   sjh.run_status,

   sjh.run_date,

   sjh.run_time,

   sjh.run_duration,

   operator_emailed = so1.name,

   operator_netsent = so2.name,

   operator_paged = so3.name

   FROM

   msdb.dbo.sysjobhistory sjh

   JOIN msdb.dbo.sysjobs sjj ON sjh.job_id = sjj.job_id

   LEFT OUTER JOIN msdb.dbo.sysoperators so1  ON (sjh.operator_id_emailed = so1.id)

   LEFT OUTER JOIN msdb.dbo.sysoperators so2  ON (sjh.operator_id_netsent = so2.id)

   LEFT OUTER JOIN msdb.dbo.sysoperators so3  ON (sjh.operator_id_paged = so3.id),

   msdb.dbo.sysjobs                 sj

   WHERE

   sjj.Name = @JobName and

   (sj.job_id = sjh.job_id)

   SELECT

   StepID, StepName, Message, RunDate AS LastRunTime,

   CASE RunStatus

   WHEN 0 THEN ‘Failed’

   WHEN 1 THEN ‘Succeeded’

   WHEN 2 THEN ‘Retry (step only)’

   WHEN 3 THEN ‘Canceled’

   WHEN 4 THEN ‘In-progress message’

   WHEN 5 THEN ‘Unknown’

   END AS RunStatus

   FROM #JobHistory

   ORDER BY LastRunTime DESC, StepID ASC

   END

   END

   GO

   EXECUTE sp_ms_marksystemobject ‘sp_ViewJobListing’

Please note :

Make sure that you do not assign permissions to the users without being sure that you are assigning these permissions to the right one. As these permissions allow the user to to see the SQL Agent jobs on the database server and this can create problems for you if you assign these permissions to a wrong user.

Also, try not to give permissions for a user to check the job of the other user on the database server which can reduce the risk of any problems in future.