Permissions to modify SQL Agent Jobs


All replies

  • Yes, add the user to MSDB database "SQLAgentOperatorRole" - SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server.

    Tuesday, February 12, 2013 3:25 AM
  • Hi Deep,

    Thanks for your reply,but i have already assign the role in MSDb,but the user can only the view/Run etc..but User are not able to edit the job.

    if its now owned by him.

    as mentioned above i would like to assign the permission who can EDIT the job.



    Tuesday, February 12, 2013 3:32 AM
  • Yes Ekbal, you are correct. I overlooked the article. It clearly mentions that the SQLAgentOperatorRole can Create/modify/delete only owned jobs. Looks like you may have to grant sysadmin/serveradmin role to the user
    • Proposed as answer by RohitGarg Tuesday, February 12, 2013 6:47 PM
    Tuesday, February 12, 2013 3:53 AM
  • Hi,

    Still looking if any other options are there ?

    Who can edit all SQL agent job(even own by others) without assigning the sysadmin role.




    Wednesday, February 13, 2013 12:28 AM
  • Hallo Ebal,

    the only possible solution will be granting access to the following stored procedures which handles jobs:

    These three procedures are located in msdb.

    To avoid direct access to msdb objects use certificates for the execution of the procs.

    Unfortunately you don't have any funny and colorful wizards which will help you modifying jobs.

    This article will give you deeper information about using certificates with stored procedures.

    Get a complete overview of all stored procedures which concerns sql server job engine here:

    Uwe Ricken

    MCSE - SQL Server 2012
    MCSA - SQL Server 2012
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    SQL Server Blog (german only)

    Wednesday, February 13, 2013 3:52 PM
  • Hi,

    After granting the Execute permission to said SP in msdb even though its NOT workings,only user can't view the Jobs made by others

    as i believe only sysadmin is the only options..

    anyways suggestions is still open.



    Thursday, February 14, 2013 3:38 AM
  • Hallo Ekbal,

    only granting access to the procs will not fit because internally these procs check whether the executing account is member of the sysadmin-role of of any other roles which need the required permissions.

    What you have to do is the development of a wrapper-proc which covers the execution with a different user. Get details concerning here:

    Long story short - you have to do the following steps if you execute the procedures in the context of a certificate:

    1. Create a certificate you want to use for the execution of the wrapper procs in msdb

    ENCRYPTION BY PASSWORD = 'myUltracomplexPassword'
    WITH SUBJECT = 'Certificate for JobManagement',
    START_DATE = '20120101',
    EXPIRY_DATE = '29991231'

    2. Backup the certificate for later implementation in the master and implementation in master database

    BACKUP CERTIFICATE cert_JobHistory TO FILE = 'C:\temp\cert_JobHistory.cer';
    USE master;
    CREATE CERTIFICATE cert_JobHistory FROM FILE = 'C:\temp\cert_JobHistory.cer';

    3. Create a login based on the certifcate and make it a sysadmin

    CREATE LOGIN login_JobHistory FROM CERTIFICATE [cert_JobHistory];
    EXEC sp_addsrvrolemember @loginame = 'login_JobHistory', @rolename = 'sysadmin';

    4. create a user in msdb for the login, create a wrapper proc for sp_update_job

    USE msdb
    CREATE USER login_JobHistory FROM LOGIN [login_JobHistory];
    CREATE PROC dbo.proc_wrapper_update_job
    @paramter_list of sp_upate_job
       EXEC dbo.sp_update_job @parameter_list

    5. add signature of certificate to the proc

    ADD SIGNATURE TO OBJECT::dbo.proc_wrapper_update_job
    BY CERTIFICATE [cert_JobHistory]
    WITH Password = ''myUltracomplexPassword';

    6. Grant EXECUTE-permission on the proc to dedicated users you want to manage the jobs

    IMHO this process is a bad workaround and - just my point of view - not practicable but I don't see any other solution than granting sysadmin privileges for the dedicated users. What you can do also is doing the modification to the mentioned procs but this is definitely not recommended because these procs are system procs and you will loose support from Microsoft!

    Get more details concerning execution of procedures by certificates by visiting the following links:

    Using certificates for execution of procs:

    Uwe Ricken

    MCSE - SQL Server 2012
    MCSA - SQL Server 2012
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    SQL Server Blog (german only)

    Thursday, February 14, 2013 6:43 AM
  • Hi Ekbal

    In the environment we are working (non DBA) teams have various SQL Agent jobs that they are responsible for the management of. When the teams are in SQLAgentOperatorRole they can do most of the things that they need to apart from editing the jobs. The only other way to allow this functionality is to give them sysadmin
     This case is already with MS connect and Brett have suggested a workaround, Please try it might work

    Reference  :  Functionaility to edit the jobs


    Saurabh Sinha

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Monday, February 18, 2013 12:34 PM
  • Hi All,

    I have similar issue. I am not able to edit SQL Server Agent Jobs. I can view and can run but cannot edit. Let me discuss with my DBA.

    After discussion found that I am not sysadmin so Can't do that:(

    Thanks Shiven:) If Answer is Helpful, Please Vote

    Thursday, November 28, 2013 11:01 PM
  • this isn't correct. you can only modify jobs owned by that user. for modifying all jobs you will need sysadmin privs
    Monday, June 09, 2014 10:02 PM
  • Hi. it doesnt work
    Wednesday, July 22, 2015 1:03 PM
  • Hi. it doesnt work

    Only sysadmin role members can edit and run jobs owned by others.

    Dan Guzman, SQL Server MVP,

    Saturday, July 25, 2015 2:34 AM
  • To add a SQL login or msdb role to a SQL Server Agent fixed database role

    1. In Object Explorer, expand a server.

    2. Expand Security, and then expand Logins.

    3. Right-click the login you wish to add to a SQL Server Agent fixed database role, and select Properties.

    4. On the User Mapping page of the Login Properties dialog box, select the row containing msdb.

    5. Under Database role membership for: msdb, check the appropriate SQL Server Agent fixed database rol

    Refer :



    Thursday, August 27, 2015 2:54 PM