Search code examples
sqlsql-servert-sqlsql-jobsql-agent

How to grant user access to view / edit Advanced option in the SQL Job?

How to grant user access to view / edit Advanced option in the SQL Job running in the SQL Agent?

User has ability to view the job. Under Job Properties -> Steps -> Advanced Option.

View options are disabled. How to grant permission for the user to view the results in the Advanced Option.

enter image description here

Solution

  • You can't give them permission to access that View button without granting them access to also make changes to the job.

    What you can do is grant access to execute msdb..sp_help_jobsteplog which will return the recorded log for the jobstep. 

    EXEC dbo.sp_help_jobsteplog @job_name = N'JobName';

    Alternatively, you could grant permission to query msdb..sysjobstepslogs directly which is where the data is stored anyway. 

    SELECT [log]
FROM msdb..sysjobstepslogs JSL
JOIN msdb..sysjobsteps JS
    ON JS.step_uid = JSL.step_uid
JOIN msdb..sysjobs J
    ON J.job_id = JS.job_id
WHERE J.name = N'JobName'