access to view job history only

Question

hi, Our devs belong to an AD group. The AD group has been added to our prod database with read only permission. (devs can only query, view tables, sp's ..). The server is sql server 2008 (non r2). We also need this AD group to be able to view jobs and job history from sql agent. (to see errors when jobs fail etc). They should not be able to start/edit/delete jobs. How can this be accomplished from a sql dba perspective ? Please be specific/detailed and attach screenshot if possible. Thanks

Answer 1

Please see:

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

Answer 2

Please see:

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

1) As Tom is pointing out, there are fixed database roles to which you can add this User in msdb.

2) If SQLAgentUserRole has to many permissions, which i would guess from your scenario description, you can just create your own custom role in msdb - just like in any other database. and grant SELECT to the system tables sysjobhistory and sysjobs - or even better create a custom view/procedure on top of them.

---

Andreas Wolter | Microsoft Certified Master SQL Server
Blog: www.insidesql.org/blogs/andreaswolter
Web: www.andreas-wolter.com

Answer 3

Go through below link

http://www.handsonsqlserver.com/how-to-grant-view-only-permission-to-users-to-see-sql-server-agent-jobs-and-read-only-access-in-all-databases/

Answer 4

All,  Links very useful. Pradeep, your link provided the most detailed explanation. Hence picking as answer. Thanks