locked
How to create SQL-login with permissions for view-only SQL-Agent-Jobs? RRS feed

  • Question

  • Could you please help me with resolving next problem:

    How to create SQL-login with permissions for view -only SQL-Agent-Jobs (he cannot create, modify or delete)?

    I am using MS-SQL-Server version: 8.00.194

    In other words : how to create in version 2000 (8.00.194) the same as 'SQLAgentReaderRole ' in version 2005 (for more details see http://technet.microsoft.com/en-us/library/ms188283.aspx).



    Wednesday, July 28, 2010 7:10 AM

Answers

  • Cannot you just DENY Permission on those stored procedures?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by David KRIZ Thursday, July 29, 2010 6:25 AM
    Wednesday, July 28, 2010 12:37 PM

All replies

  • Try adding the user to the TargetServersRole in msdb and you'll be able to view (but not modify) the jobs.

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, July 28, 2010 8:00 AM
  • I have tried adding the user to the TargetServersRole in msdb.

    It has one problem - user can Start and Stop jobs.

    Solution would be copy role TargetServersRole to some new (for example with name SqlJobsViewOnly ) and Deny Execute permissions on stored procedures 'sp_start_job ' and 'sp_stop_job '.

    Do you know how to copy database role ?


    __________________________________________________________ If isn't above described anything, the following applies: Technical details: * OS: Windows Server v2003, English, Standard Edition, SP2 ** My User-Account is member of 'Administrators' local security group. * MS-SQL-Server: v2005, English, Standard Edition, SP2 ** My User-Account is member of 'SysAdmin' db-role.
    Wednesday, July 28, 2010 12:31 PM
  • Cannot you just DENY Permission on those stored procedures?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by David KRIZ Thursday, July 29, 2010 6:25 AM
    Wednesday, July 28, 2010 12:37 PM
  • David,

    I used to use TargetServersRole extensively on SQL Server 2000, without the symptom of the users being able to start and stop jobs.  I just went and found an old post from when I was doing that.

    http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server/13815/SQL-Server-Jobs-permissions-for-non-owners

    In fact, someone who needed to create jobs was given a second login that did not have that restriction. (If I remember correctly, there is code in the stored procedures that checks directly for the TargetServersRole.)

    So, what other rights do your users have?   Have they been granted rights directly to some stored procedures? Any other system role or database role that they are members of?

    RLF

    Wednesday, July 28, 2010 2:47 PM
  • Good idea! It works fine. Thank you very much!

    :-)


    __________________________________________________________ If isn't above described anything, the following applies: Technical details: * OS: Windows Server v2003, English, Standard Edition, SP2 ** My User-Account is member of 'Administrators' local security group. * MS-SQL-Server: v2005, English, Standard Edition, SP2 ** My User-Account is member of 'SysAdmin' db-role.
    Thursday, July 29, 2010 6:26 AM
  • I am using MS-SQL-Server version: 8.00.194

    But On SQL Server 2000, TargetServerRole available from Service Pack 3.


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    Thursday, July 29, 2010 8:19 AM
  • I am having the opposite reaction.  I WANT this user to be able to see and execute [start/stop] the jobs but do not want the user to be a sysadmin.

    When I assigned the user to the 'TargetServerRole' the user can SEE the jobs but receives an error message 'cannot execute sp_start_job' when trying to run the job.

    Does anyone know why ?

     

    Thank you in advance for your thoughts.

     

    Thursday, September 2, 2010 5:51 PM
  • Ellen,  What you describe is actually the way that TargetServerRole is supposed to behave.  

    I don't know why David did not have that experience as his post reveals.  I suspect somebody figured out what to change in the msdb code in order to remove the restriction, but I don't currently know what that would have been.

    RLF

    Thursday, September 2, 2010 9:36 PM