SQL Agent permissions

Question

SQL Agent permissions

robert.gerald.taylor

SSChampion

Points: 13133
More actions
June 4, 2014 at 2:48 pm

#287227

I've got an AD user group setup Doman\BI Admins that I've given SQLAgentOperatorRole, SQLAgentReaderRole and SQLAgentUserRole membership in the msdb database. The users in the BI Admins group can see and run SQL Agent jobs, but they cannot edit the steps in a SQL Agent job.
What permissions am I missing to allow these users to edit a SQL Agent job?
My limited Bing/Google search only mentions the three roles (mentioned above).
Thanks,
Rob

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

robert.gerald.taylor SSChampion Points: 13133 More actions · Answer 1

Another wrinkle to this: It appears that the SQL Agent permissions allows the user to modify jobs owned by them, but not by others. In my situation, the job is owned by sa, and the users cannot modify this SQL Agent job.

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

How do others handle this? Just have the DBA modify existing SQL Agent jobs or promote from dev to prod.

Thanks,

Rob

T2000 SSC Eights! Points: 894 More actions · Answer 2

Hi,

had the same issue for our DEV team required extra Agent job access. This is how i got around it.

1 ActDir group. Hold all user accounts that need such access

2 Added ADgrp to msdb with access to the 3 SQLAgentxxx roles

3 Created a new SQL Credential, and used a sysadmin account (or SA) as Identity for it

4 Created a SQL Proxy account, added the previously created Credential & allow access to the required 'subsystems' & added my ActDir grp to the proxies' Principals.

That worked for. Now every AD account in the grp has Agent Job security without having sysadmin access themselves, but using the proxy account.

No guru, but worked for us. Maybe it can help you.

rgd

T.

robert.gerald.taylor SSChampion Points: 13133 More actions · Answer 3

T2000 (6/5/2014)
had the same issue for our DEV team required extra Agent job access. This is how i got around it.
1 ActDir group. Hold all user accounts that need such access
2 Added ADgrp to msdb with access to the 3 SQLAgentxxx roles
3 Created a new SQL Credential, and used a sysadmin account (or SA) as Identity for it
4 Created a SQL Proxy account, added the previously created Credential & allow access to the required 'subsystems' & added my ActDir grp to the proxies' Principals.
That worked for. Now every AD account in the grp has Agent Job security without having sysadmin access themselves, but using the proxy account.
No guru, but worked for us. Maybe it can help you.
rgd
T.

Thank you, T.

I'll give that a try.

Thanks,

Rob