0

I have a SQL Agent job owned by 'sa'. I created a 'SQL Login' and granted permission to 'msdb' database with the following Database role membership:

SQLAgentOperatorRole SQLAgentReaderRole SQLAgentUserRole

The user can execute and view the job. But, does not have the ability to edit the job. Is there a way to edit SQL Server Agent job without being owner of the job or 'sa'?

jmrio
  • 133
  • 1
  • 2
  • 8
  • 2
    either grant permissions to that account to edit the job, or use a different account. any other method would basically make the security systems pointless. "you're not the owner, you don't have the rights, but go right on into the bank vault and help yourself to anything you want" – Marc B Oct 29 '14 at 18:50
  • As per my knowledge only owner can edit the job or need to be 'sa'. There are few people in the team needs this access. But, we can't grant them 'sysadmin' server role. So, I am trying to see if there is any alternative way to achieve this. – jmrio Oct 29 '14 at 18:56
  • 2
    What are the *specific* attributes of the job that users need to modify? You could of course allow them to update `sysjobs`/`sysjobsteps` directly, or write wrapper procedures to control how they do that. This bypasses all the "is owner or sysadmin" checks in all the job-related procedures, but the downside is they would have to write the code, not use the UI. – Aaron Bertrand Oct 29 '14 at 19:44
  • Steps --> command is the attribute we want to edit. We want to edit it through Management Studio. – jmrio Oct 29 '14 at 20:08
  • 2
    Well, I want free cake. Sometimes you need to sacrifice some requirements to meet others. – Aaron Bertrand Oct 29 '14 at 20:40
  • So, you are suggesting the only option is to update the job directly in msdb database? Also, what do you mean by sysjobs/sysjobsteps? Our users have to have the ability to modify the user created job. – jmrio Oct 30 '14 at 16:08
  • I am going to mark this question as answered. Aaron Bertrand, can you please paste your comments in the answer section? I will mark it as answered. Thanks for the help. – jmrio Oct 31 '14 at 16:08
  • I had the same problem and I build a workaround In this scenario is obligatory, that all job names follow the same nomenclature. The name of a job starts with the name of the associated database. In the every db, there is a schema jobcontrol with a table (job_name, job_owner, moddate) In this table, all jobs associated to the data base are listed. A user can change the ownership in the table to his own login. If there is a change, an admin job will validate the change in this table and switch the ownership to the job. The switch back comes after some hrs or if the user change in the jobtable – Joe Platano Dec 01 '14 at 21:30

0 Answers0