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

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

• Marked as answer by Allen Li - MSFTMicrosoft contingent staff, Moderator Friday, February 22, 2013 2:40 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

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 RohitGargMicrosoft community contributor Tuesday, February 12, 2013 6:47 PM

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

Hallo Ebal,

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

• sp_update_job
• sp_update_jobschedule
• sp_update_jobstep

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

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

• sp_update_job
• sp_update_jobschedule
• sp_update_jobstep

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

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

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

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:(

this isn't correct. you can only modify jobs owned by that user. for modifying all jobs you will need sysadmin privs

Hi. it doesnt work

Hi. it doesnt work

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