Subscribe to this blog
Briefcase
Print
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'), 0) = 1)
OR (ISNULL(IS_MEMBER(N'SQLAgentReaderRole'), 0) = 1)
OR ( (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1) AND
(EXISTS(SELECT * FROM msdb.dbo.sysjobservers js
WHERE js.server_id <> 0 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:
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.
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.