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:

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
CREATE PROCEDURE [dbo].[sp_ViewJobListing]
    @JobName VARCHAR(255)=NULL
    IF OBJECT_ID('tempdb..#Results')>0
        DROP TABLE #Results
    CREATE TABLE #Results
        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
    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
                msdb..sysjobhistory sho
                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
        s.Name = ISNULL(@JobName, s.Name)
    ORDER BY s.Name
    IF @JobName IS NOT NULL
        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
            operator_emailed = so1.name,
            operator_netsent = so2.name,
            operator_paged = so3.name
            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
        sjj.Name = @JobName and
        (sj.job_id = sjh.job_id)
            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
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.