Permissions to edit SQL 2008 R2 jobs

• Express12

  SSCoach

  Points: 18514

  More actions

  January 5, 2012 at 12:12 pm

  #253003

  Need best way to grant permission to allow one of my Power users (a pseudo/DBA) to edit a SQL Server 2008 R2 job. This user does not retain sysadmin on the box nor should they.

  This user also does not OWN the job (nor should they).. these 3 roles (SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole) are limited in that user can edit job ONLY if they own the job.

  I tried creating a new credential (referencing an acct w/ sysadmin permissions), creating a new Proxy acct referncing the new credential and adding the NT Login as a Principal -- did not work. My power user still cannot edit the SQL job.

  I think I'm missing something simple here..

  BT

• Chuck Hottle

  SSCarpal Tunnel

  Points: 4777

  More actions

  January 5, 2012 at 12:55 pm

  #1429310

  I think they need to own the job in addition to being a member of the applicable msdb role. I don't think there is any way around having them own it.

  http://msdn.microsoft.com/en-us/library/ms188283.aspx