id="info"

Tech & Work

How do I assign permissions to users to see SQL Agent Jobs?

You can give your users rights to check if reports have run or jobs are complete without granting full admin rights. Here's how you can do so by using SQLAgentReaderRole in SQL Server.

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.

About

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.

Editor's Picks