Grant access to view Jobs and History only

  • Hi All

    Got a requirement to allow users to view all jobs and history only via Job Activity Monitor.

    Don't want to give them SQLAgentUser/Reader roles as this gives to many permissions and allows users to create jobs etc. Did think about deny execute on sp_add_job but I don't like changing anything thats MS shipped as default, wont be a problem doing so as sysadmins could still create jobs.

    But just after the minimum permissions to view SQL Agent in SSMS and then only view Jobs and their history?

    Did create a custom role but that gives access via T-SQL but not via the GUI as it doesn't show the Agent sub folder in object explorer.

  • Give access to the role SQLAgentReaderRole is MSDB Database.

    -Roy

  • They already have that, the problem is that the roles allow you to create jobs, this goes against the auditing practises the client has in place where only sysadmins should be able to do that.

    Yes I can deny execute on sp_add_job but to SQLAgentUserRole but really don't want to change anything thats shipped as part of the product.

  • You can make it granular...

    You could give just execute permission on sp_help_jobhistory (They need to know the name of the job I think)

    Or give them select permission to sysjobhistory table.

    -Roy

  • Yeah but they want it via the GUI not via script.

    The only way I know to get the SQL Agent to show up in SSMS is to be part of the SQLAgent roles.

    Was after another way other than granting SQLAgent roles to get to via SQL Agent in SSMS

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply