locked
SQL Server jobs permission RRS feed

  • Question

  • Hello

    How do You give a user permission to see only some Jobs?.

    The 'sql agent opreator role'  gives permission to all.

    Monday, January 31, 2011 1:58 PM

Answers

  • Unfortunately, there is not a built-in method to do this in a general fashion. 

    SQLAgentOperatorRole not only gives the user permission to see all jobs, but it also gives him extra capabilities to manages those jobs.

    SQLAgentReaderRole will give the user permission to see all jobs, but only to manage the jobs that the user owns.  This is a safer membership than the SQLAgentOperatorRole.

    It is possible to create a stored procedure that would report on only selected jobs, if that would suit your purposes.

    RLF

    • Proposed as answer by SivaReddyG Wednesday, February 2, 2011 8:13 PM
    • Marked as answer by WeiLin Qiao Wednesday, February 9, 2011 9:31 AM
    Monday, January 31, 2011 4:15 PM

All replies

  • Unfortunately, there is not a built-in method to do this in a general fashion. 

    SQLAgentOperatorRole not only gives the user permission to see all jobs, but it also gives him extra capabilities to manages those jobs.

    SQLAgentReaderRole will give the user permission to see all jobs, but only to manage the jobs that the user owns.  This is a safer membership than the SQLAgentOperatorRole.

    It is possible to create a stored procedure that would report on only selected jobs, if that would suit your purposes.

    RLF

    • Proposed as answer by SivaReddyG Wednesday, February 2, 2011 8:13 PM
    • Marked as answer by WeiLin Qiao Wednesday, February 9, 2011 9:31 AM
    Monday, January 31, 2011 4:15 PM
  • Thanks.

    Stored procedure should be ok.

    Would this only report selected jobs to the user I selected?

    I would still be able to see all jobs as SA.

    Tuesday, February 1, 2011 10:56 AM
  • Creating a stored procedure for others to use would not limit your own rights.  This is an alternate mechanism.

    In the following post, I created code to allow a non-job-owner to start a job he did not own.  This approach can be modified to provide other services on the jobs, such as examining history.  Look it over and see if it helps you decide how to implement a stored procedure.

    http://social.msdn.microsoft.com/Forums/en/sqltools/thread/f5df9c71-9006-4232-b99c-91d254f92788

    RLF

     

    Tuesday, February 1, 2011 4:59 PM
  • The stored procedure method is an alternative form of access to job information.  It would not replace your sysadmin rights.

    I wrote a procedure to delegate rights to execute sp_start_job.  The code for the procedure can be found in the following post:

    http://social.msdn.microsoft.com/Forums/en/sqltools/thread/f5df9c71-9006-4232-b99c-91d254f92788

    Of course, that is not providing information on the job, such as history, details on the job configuration, and so forth.  However, this approach could provide a template for creating a procedure that will satisfy the information that you want to expose.

    Hope that helps,
    RLF

    Tuesday, February 1, 2011 8:12 PM
  • Thanks For the help.

    Never wrote a stored Procedue before.

     

     Could you Modify this to provide the service?

     

     

    Wednesday, February 2, 2011 10:14 AM