Is there a READONLY setting for SQL 2008 R2 SQL Agent Job monitor?

Question

Is there a READONLY setting for SQL 2008 R2 SQL Agent Job monitor?

Express12

SSCoach

Points: 18514
More actions
March 9, 2012 at 6:33 am

#269799

Is there a role or permission that can be assigned to a user to have READONLY access to the SQL 2008 R2 SQL Agent monitor -- but not the ability to edit the jobs?
We need to provide this access to an operational team enabling them to monitor their SQL jobs w/out having to rely on DBA's.
BT

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

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

RTaylor2208 SSChampion Points: 13298 More actions · Answer 1

As far as I am aware only Sysadmins have permission to view the SQL Agent monitor. One alternative is to provide access through a custom coded dashboard that runs queries against msdb table sysjobs, sysjonsteps etc etc.

The dashboard would require a connection string using an account that was a member of the db_datareader role in the msdb database.

MCITP SQL 2005, MCSA SQL 2012

CapnHector SSCoach Points: 16143 More actions · Answer 2

you can look at the SQLAgentReaderRole. this might be the solution you are looking for. check out the msdn article at the link

http://msdn.microsoft.com/en-us/library/ms188283.aspx

use msdb

EXECUTE sp_addrolemember 'SQLAgentReaderRole', 'username'

GO

EDITED TO ADD LINK

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Express12 SSCoach Points: 18514 More actions · Answer 3

thanks .. omitted a 'specific' in our original question here... Need permisison to view SQL Server Agent "Job Activity Monitor".

I added the user's Windows Login to both MASTER and MSDB database role: SQLAgentReaderRole.

User was then able to see SQL Server Agent and all the jobs BUT he cannot see the JOB ACTIVITY MONITOR. He receives error:

Cannot show requested dialog. Addtional Information: Unable to execute requested command. jobs (SqlManagerUI)

User is using SQL 2008 R2 Developer edition SSMS..

BT

george sibbald SSC Guru Points: 104210 More actions · Answer 4

grant them view server state, also sqlagentreaderrole should be enough.

---------------------------------------------------------------------