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.
SQLAgentReaderRole
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:
use msdb
EXECUTE sp_addrolemember
@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 jobs
In 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.
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'
Be Careful
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.
