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

If you have several SQL Agent jobs on your system, it is inevitable that at some point 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 (http://cgi.cnet.com.au/link/?id=23624) 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 them, 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.