Rotating Header Image

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.

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

6 Comments

  1. [...] Creating a REAL SQLAgentReader role [...]

  2. Murali says:

    This is gud workaround. Thanks.

  3. Ernest says:

    Perfect. Had to do the same thing (Devs are not allowed to create jobs on prod systems so can’t use sqlagentreader).

  4. Dave says:

    Thanks — this seems to be just what I was looking for.

    Are you still using this role as defined above? Have you found any gotcha’s?

  5. Mark says:

    If you don’t like the idea of a role in your system database, you could just grant the sqlagentreader role to a group like ‘TheDevelopers’ and then deny execute on those procs to that group or to individual logins. This approach is not ideal either. You have to alter privilges on the system sp’s for every group or login but technically speaking you do not have a role in your system database… but you don’t have the advantages of a Role which is easy to grant to a user(s).

    A better idea… MS SQL Server: Please provide an Agent Reader Role!!

  6. Natalia says:

    All you need to do is to grant db_reader role together with SQLAgentReaderRole in msdb for that user and that will fix all issues. User will be able to browse jobs, steps, schedules etc., but will not be able to update the job.

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.