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.