locked
Access to SQL Server Jobs RRS feed

  • Question

  • Hello folks,

    Would like to grant group of users ( windows logins) to CREATE / MODIFY / DELETE / SCHEDULE jobs on SQL Server 2005.

    But can not grant users SYSADMIN privileges. I know that by granting users SQLAgentOperatorRole, SQLAgenetReaderRole and SQLAgentUserRole the User will be able to create jobs but they will be the owner of the newly created job and other users can not MODIFY / VIEW / RESCHEDULE the jobs. Is there any way to customize permissions to windows logins to have permission to CREATE / MODIFY / DELETE / SCHEDULE jobs without granting them SYSADMIN privilages ?

    Appreciate your reply

     

    Thank You

    Arun


    aroon
    Monday, August 23, 2010 5:29 PM

Answers

  • In your case I think only sysadmin server role will fix all your 'problems'
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, August 24, 2010 8:29 AM

All replies

  • In your case I think only sysadmin server role will fix all your 'problems'
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, August 24, 2010 8:29 AM
  • I have to agree with Uri on this one Arun.  What you are asking to do is a major security issue because if you let anyone modify any jobs then they can affects non-ownership databases/server without you knowing it.

    Therefore it is best to constraint this security to sysadmin, who can make sure the job being created does not inversely affect other databaes and server functionality.

    May I ask why do you want to give this access? Maybe we can find a better alternative?

     

    Cheers.
    Mohit.

    Thursday, August 26, 2010 3:28 PM
  • we are just 2 dba's managing multiple application databases. this requirement is in non production server. the developers want to create jobs for testing purpose but we can't give them sysadmin because that will allow them access to sensitive info in other databases. if i grant them the 3 roles in the msdb database they can create there own jobs and test themselves without calling us each and everytime. the problem is we don't have time to assist them in creating jobs. i don't like that approach of sql login and msdb roles assigned to them. so am looking for alternative approach. appreciate your  feedback.

    arun


    aroon
    Thursday, August 26, 2010 5:15 PM