If you have several SQL Agent jobs on your system, it is inevitable that at some point in some of your in-house users or developers will contact you asking if a certain report has ran or if a certain job is enabled. The question is...how do you assign users the rights to see these jobs? In SQL Server 2000, it was difficult to allow users to see the SQL Server Agent jobs on your database server without giving the user sysadmin permissions. SQL Server 2005 makes assigning this permission much easier through the use of new database roles in the msdb database.
The SQLAgentReaderRole is a database role located in the msdb database. It is one of three new roles in this database aimed at allowing the database administrator the ability to assign more granular permissions when it comes to the administration of SQL Agent jobs. Assigning a user or group to be a member of this role allows the user to see any SQL Agent job located on the server instance, even the jobs in which that user does not own. The user can see the job, along with any history saved to the job. However, the group is not allowed to execute the jobs. To add a user as a member of the SQLAgentReaderRole, you can execute the following command:
@rolename = 'SQLAgentReaderRole',
@membername = 'username'
It is also worth mentioning the other two SQL Agent roles available in SQL Server 2005. The SQLAgentUserRole allows users to create jobs and to manage the jobs that they create. The SQLAgentOperatorRole allows users all of the rights assigned to the SQLAgentReaderRole along with the permissions necessary to execute local jobs that they do not own.
Custom Code for viewing jobsIn the event that you are not comfortable with assigning SQLAgentReaderRole permissions or if you are still on SQL Server 2000, there is still hope. The custom system stored procedure below is a listing of the jobs on the system, and some detail to go along with the job. Once you've created the procedure, you can assign execute permissions to the users that need to see the jobs on the system.
CREATE PROCEDURE [dbo].[sp_ViewJobListing]
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
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,
CASE WHEN xp.running = 0 THEN 'Not Running' ELSE 'Executing...' END AS Status,
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
sho.job_id = xp.job_id AND
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
s.Name = ISNULL(@JobName, s.Name)
ORDER BY s.Name
IF @JobName IS NOT NULL
CREATE TABLE #JobHistory
INSERT INTO #JobHistory
operator_emailed = so1.name,
operator_netsent = so2.name,
operator_paged = so3.name
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),
sjj.Name = @JobName and
(sj.job_id = sjh.job_id)
StepID, StepName, Message, RunDate AS LastRunTime,
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
ORDER BY LastRunTime DESC, StepID ASC
EXECUTE sp_ms_marksystemobject 'sp_ViewJobListing'
In today's tip I looked at how you can allow users on your system to see the SQL Agent jobs on the database server. However, just because you are able to assign these permissions, doesn't mean that you should do so recklessly. Take care when assigning these types of permissions to users. If users absolutely need access to view these permissions and you feel comfortable with then, then by all means give them what they need to do their job. If they have a one time request, then give them the information if they need it, or point them to one of your users who you have given permission to view this information.
Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.