none
Permissions to modify SQL Agent Jobs

Answers

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.

    http://msdn.microsoft.com/en-us/library/ms188283(v=sql.105).aspx

    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.

    Regards


    EA



    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
    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.

    Regards

    Ekbal


    EA

    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.
    http://msdn.microsoft.com/en-us/library/bb283630(v=sql.105).aspx

    Get a complete overview of all stored procedures which concerns sql server job engine here:
    http://msdn.microsoft.com/en-us/library/ms187763.aspx


    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
    http://www-db-berater.de
    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..

    http://msdn.microsoft.com/en-us/library/ms188283(v=sql.100).aspx

    anyways suggestions is still open.

    Regards


    EA

    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:

    http://msdn.microsoft.com/en-us/library/ms178106(v=sql.90).aspx

    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

    CREATE CERTIFICATE cert_Jobs
    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;
    GO
    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];
    GRANT AUTHENTICATE SERVER TO [login_JobHistory];
    GO
    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
    GO
    CREATE USER login_JobHistory FROM LOGIN [login_JobHistory];
    GO
    CREATE PROC dbo.proc_wrapper_update_job
    @paramter_list of sp_upate_job
    WITH EXECUTE AS OWNER
    AS
    BEGIN
       EXEC dbo.sp_update_job @parameter_list
    END

    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:

    AUTHENTICATE SERVER: http://support.microsoft.com/kb/906549
    Using certificates for execution of procs: http://msdn.microsoft.com/en-us/library/bb283630(v=sql.105).aspx


    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
    http://www-db-berater.de
    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

    Thanks

    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