Creating a REAL SQLAgentReaderRole

So if you follow me blog you will probably remember this post from earlier today:

http://benchmarkitconsulting.com/colin-stasiuk/2009/01/21/sqlagentreaderrole-not-so-reader/

Below is the solution I’m working with to give developers access to the Job Activity Monitor but NOT the ability to create new jobs.

I know adding a role to a system database is not ideal but I’m open to other suggestions


USE [msdb]
GO
CREATE ROLE [SQLAgentReadOnlyRole] AUTHORIZATION [dbo]
GO
EXEC sp_addrolemember N'SQLAgentReaderRole', N'SQLAgentReadOnlyRole'
GO
DENY EXECUTE ON OBJECT::msdb.dbo.sp_add_job TO SQLAgentReadOnlyRole
DENY EXECUTE ON OBJECT::msdb.dbo.sp_add_jobserver TO SQLAgentReadOnlyRole
DENY EXECUTE ON OBJECT::msdb.dbo.sp_add_jobstep TO SQLAgentReadOnlyRole
DENY EXECUTE ON OBJECT::msdb.dbo.sp_update_job TO SQLAgentReadOnlyRole
DENY EXECUTE ON OBJECT::msdb.dbo.sp_add_jobschedule TO SQLAgentReadOnlyRole

So after you create your new SQLAgentReadRoleRole you can add role members to it and they will be able to view the Job Activity Monitor but will not be able to create new jobs… use at your own risk as like I said… I’m still working with it but this is the direction I’m heading right now.

If anyone has a more elegant solution I’d be very interested in seeing it.