SQL Agent Jobs – Permissions

March 9, 2009 / Dedicated Server Hosting vps hosting

SQLAgentReaderRole can solve the problems of giving rights to the users for administration of SQL Agent jobs. It is located in MSSQL Database and is a Database role. With the help of this it is possible to assign the user the permissions to see any SQL Agent job in the database server. However, this can also create some problems as the users will be able to see the jobs which are owned by other users. It is also possible for the users to check the history of the job. But the group of users will not be able to execute the job and you will have to add the user to SQLAgentReaderRole as a member. Follow the steps given below to do so :

Enter the following commands

use msdb

EXECUTE sp_addrolemember

@rolename = ‘SQLAgentReaderRole’,

@membername = ‘username’

There are 2 more SQL Agents role which are available in SQL Server 2005.

  1. SQLAgentUserRole – This will allow the users to manage the jobs after they have created the job.
  2. SQLAgentOperatorRole – The permissions assigned to SQLAgentUserRole are also assigned to SQLAgentOperatorRole. However, SQLAgentOperatorRole also provides permissions to the users to start the local jobs that they do not own.
    Custom Code for viewing jobs

    If you do not want to assign SQLAgentReaderRole then you have an option. Even if you do not have  SQL server 2005 then too you do not have to worry as there is an option. Below is the listing of jobs on the system with the custom system and also the details of the specific jobs. You can assign permissions to the users to view the jobs on the system according to you after you have created the procedure given below.

    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’

Please note :

Make sure that you do not assign permissions to the users without being sure that you are assigning these permissions to the right one. As these permissions allow the user to to see the SQL Agent jobs on the database server and this can create problems for you if you assign these permissions to a wrong user.

Also, try not to give permissions for a user to check the job of the other user on the database server which can reduce the risk of any problems in future.