http://www.sqlservercentral.com/blogs/brian_kelley/2009/01/22/read-only-access-to-sql-server-jobs/

Printed 2014/04/17 02:03PM

Read-Only Access to SQL Server Jobs

2009/01/22

This question comes up a lot: how do I give read-only access to job status to a group of people? The answer, at least in SQL Server 2005 and 2008, seems simple: add them to the SQLAgentReaderRole role. But this doesn't actually work, as this role also has the ability to create jobs. That's not what the name implies, but that's the rights it has. And you're rather limited in the roles that can be used. Why? If you do an sp_helptext on sysjobs_view (which is what the Job Status Monitor makes a call to - you can see this if you do a Profiler or server-side trace), you'll see this nice little where clause:

WHERE (owner_sid SUSER_SID())  
   OR (
ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 01
)  
   OR (
ISNULL(IS_MEMBER(N'SQLAgentReaderRole'), 01
)  
   OR ( (
ISNULL(IS_MEMBER(N'TargetServersRole'), 01
) AND  
        (EXISTS(
SELECT FROM 
msdb.dbo.sysjobservers js   
         
WHERE js.server_id <> AND js.job_id jobs.job_id))) -- filter out local jobs  
 

So how do you get around giving users the ability to create jobs? This was a question Colin Stasiuk asked yesterday on Twitter (his Twitter handle). After a bit of research he came up with this:

Creating a REAL SQLAgentReader role

It's not pretty, but without modifying sysjobs_view or giving more access than what a user needs, there's probably no way around it. Colin is still working to find a more elegant solution, so if you have one, pop over to his blog or hit him up on Twitter with your suggestions.

 


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.