Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Permissions to modify SQL Agent Jobs

답변됨 Permissions to modify SQL Agent Jobs

  • Tuesday, February 12, 2013 3:21 AM
     
     
    Sign In to Vote
    0

    Permissions on SQL Server Agent

    I would like to assign the permission to user ,who can edit all SQL agent job(even own by others) without assigning the sysadmin role.


    is it possible?

    regards

    EA

     

All Replies

  • Tuesday, February 12, 2013 3:25 AM
     
     Answered
    Sign In to Vote
    0

    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:32 AM
     
     
    Sign In to Vote
    0

    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



    • Edited by Ekbal Ansari Tuesday, February 12, 2013 3:33 AM
    •  
     
  • Tuesday, February 12, 2013 3:53 AM
     
     Proposed
    Sign In to Vote
    0
    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
     
  • Wednesday, February 13, 2013 12:28 AM
     
     
    Sign In to Vote
    0

    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 3:52 PM
     
     Proposed
    Sign In to Vote
    0

    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)

     
  • Thursday, February 14, 2013 3:38 AM
     
     
    Sign In to Vote
    0

    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 6:43 AM
     
      Has Code
    Sign In to Vote
    0

    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)

     
  • Monday, February 18, 2013 12:34 PM
     
     
    Sign In to Vote
    0

    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