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.
- SQLAgentUserRole – This will allow the users to manage the jobs after they have created the job.
- 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 jobsIf 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.