Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.

Read-Only Access to SQL Server Jobs

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.

 

Comments

Posted by Saravanan on 25 January 2009

After repeated failures got a solution. Create another Role Say Read_Only_SQLJobs, provide the TargetServerRole permissions to it and also provide Deny permissions on the Create/Modify/Delete permissions or Deny the execute permissions of the add/modify/delete procedures.

It brings up the create job screen, but when clicked OK, does not create a job and pops error.

Leave a Comment

Please register or log in to leave a comment.