locked
SQLAgentOperatorRole RRS feed

  • Question

  • Hi All,

    I have a question regarding SQLAgentOperatorRole.

    I found the information from

    http://msdn.microsoft.com/en-us/library/ms188283.aspx

    "SQLAgentOperatorRole members can enable or disable local jobs they do not own by using the stored procedure sp_update_job and specifying values for the @enabled and the @job_id (or @job_name) parameters. If a member of this role specifies any other parameters for this stored procedure, execution of the procedure will fail."

    Can any one tell me what does it means, beacuse i found that member of SQLAgentOperatorRole (which are not member of sysadmin fix server role) are able to enable/desable any local job (owned and non owned)

    Thanks,

    Mohd

    Sunday, April 4, 2010 9:57 PM

Answers

  • Mohd..

    Users in this role can enable or disable the all the jobs irrespective of owner. The user can enable or disable the jobs through GUI or T-SQL. If you are going to perform using T-SQL then you need to use sp_update_job proc in msdb database. The user can only make use of enabled parameter so that he \ she is allowed to perform enable or disable.

    With sp_update_job procedure you are allowed to do some more things, lets say you can rename the job.. However when the user exists in SQLAgentOperatorRole role cannot rename (@new_name parameter) or use other parameter of the procedure, only allowed parameter is @enabled.


    Vidhya Sagar. Mark as Answer if it helps!
    • Proposed as answer by Tom Li - MSFT Wednesday, April 7, 2010 1:52 AM
    • Marked as answer by Tom Li - MSFT Thursday, April 15, 2010 8:25 AM
    Tuesday, April 6, 2010 4:57 PM

All replies

  • I dont see any difference in two statements that you have mentioned . For the jobs owned by SQLoperatorrole - they can enable / disable the job straightaway and for the jobs that are not owned they have to use the stored procs - summing them up anyway conveys that this role can DISABLE \ ENABLE all the local jobs irrelevant of their owenrship( only the method differs)

    Thanks, Leks
    Sunday, April 4, 2010 10:29 PM
  • Hi Lekss,

    Thanks for replying.

    What i am trying to say here is

    I found that the jobs that are not owned by SQLoperatorrole can also be enable / disable straightaway without using the stored procs.

    Please let me know if this question is still not clear to you/all then i would like to make it more clear for you all.

    Thanks,

    Mohd

     

    Monday, April 5, 2010 7:37 AM
  • Hi All,

    Any update in this????????????????

    Thanks,

    Mohd

    Tuesday, April 6, 2010 1:22 PM
  • Mohd..

    Users in this role can enable or disable the all the jobs irrespective of owner. The user can enable or disable the jobs through GUI or T-SQL. If you are going to perform using T-SQL then you need to use sp_update_job proc in msdb database. The user can only make use of enabled parameter so that he \ she is allowed to perform enable or disable.

    With sp_update_job procedure you are allowed to do some more things, lets say you can rename the job.. However when the user exists in SQLAgentOperatorRole role cannot rename (@new_name parameter) or use other parameter of the procedure, only allowed parameter is @enabled.


    Vidhya Sagar. Mark as Answer if it helps!
    • Proposed as answer by Tom Li - MSFT Wednesday, April 7, 2010 1:52 AM
    • Marked as answer by Tom Li - MSFT Thursday, April 15, 2010 8:25 AM
    Tuesday, April 6, 2010 4:57 PM
  • Hi Vidya Sagar,

    Thats what i can see but after reding the permissions in

    http://msdn.microsoft.com/en-us/library/ms188283.aspx

    i was just confuse that why they are putting Yes 3 insted of simply putting yes. Any ways if this the case then now it is clear to me, this thread can be closed thanks a lot

    Thabks,

    Mohd

    Wednesday, April 7, 2010 12:28 PM
  • Mohd.. Thats just a information to know the readers that the same can be done via T-SQL. That's y they have given the procedure name and the parameter to be used, you can find the same for schedule disable or enable option.


    Vidhya Sagar. Mark as Answer if it helps!
    Wednesday, April 7, 2010 8:57 PM