Results 1 to 2 of 2

Thread: SQL Agent Roles

  1. #1
    Join Date
    Aug 2005
    Location
    D/FW, Texas, USA
    Posts
    78

    Unanswered: SQL Agent Roles

    I am apparently missing something in the SQL 2005 Agent Roles. I want to give a set of developers access to view the History and Properties for Agent Jobs on a server. I assumed that the SQLAgentReader Role would do exactly that. However, SQLAgentReader is a member of SQLAgentUser so now they have the ability to create their own jobs as well. (Which I don't want.)

    Is there an easy way to get Reader without the ability to create, edit or start anything?

    The next thing that comes to mind, would be to Add the users to the SQLAgentReader Role and then explicitly deny execute on the add/edit job stored procedures. The other thought I had was to just set up db_reader, or grant select access on the necesary tables/views, but I don't think Management Studio would understand that, so I'd have to write a separate GUI to access the data.
    --wayne
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Explicitly deny execute permission on the following after enrolling those logins into SQLAgentReaderRole:

    msdb.dbo.sp_add_job
    msdb.dbo.sp_add_jobserver
    msdb.dbo.sp_add_jobstep
    msdb.dbo.sp_update_job
    msdb.dbo.sp_add_jobschedule
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •