Give permissions for SQL Agent

Question

Post reply

Give permissions for SQL Agent

Joao Baptista

SSC Enthusiast

Points: 136
More actions
August 22, 2006 at 8:48 am

#62030

Hi,
I need to give permissions to access the SQL Agent jobs to a DEV team.
I've tried to give them permissions in the msdb database sp's , tables and view that support the SQL Agent, but it didn't work.
Any ideias???

Viewing 6 posts - 1 through 5 (of 5 total)

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

Clive Strong SSChampion Points: 11222 More actions · Answer 1

What exactly do you want your DEV team to be allowed to do?

Just run scheduled jobs? Add, alter, delete them?

Lance Sanders SSC Eights! Points: 903 More actions · Answer 2

Are they running integrated security?

Do they all have the same level of db access?

Does the team have a domain group that they all belong to?

If so, you can try making the domain group the owner of the jobs.

You may have to change the SQL Agent service account and set up a proxy account for non-SA users. If you do this, remember that the proxy also controls xp_cmdshell, so watch the permissions carefully.

Joao Baptista SSC Enthusiast Points: 136 More actions · Answer 3

They need to 'see' the jobs and be able to run them. We are using integrated security.

I got into a solution that is working, but its a workaround, I've joined them into the TargetServersRole of the msdb and it worked. But if i try to give them individualy the permissions just like in the TargetServersRole it doesn't work.

Monte Pix-294031 Old Hand Points: 371 More actions · Answer 4

I think you need to set up a "proxy" user in the SQL Server Agent properties. The proxy user should be a "windows" user that has permissions to run Agent.

cmille19 SSCertifiable Points: 5960 More actions · Answer 5

This is a bit of a hack, however I've used it from SQL Server 7.0 through SQL Server 2000 including all Service Packs:

1. Modify sysjobs_view by adding two lines to end:

OR ((ISNULL(IS_MEMBER(N'MyJobExec'), 0) = 1)

AND name like 'MyJob%')

2. Create a role in msdb called MyJobExec or whatever you want to call it as long as it matches the first line of OR condition.

3. Add the NT logins or groups you want to have permission to the job to the newly create role

4. Name the job MyJob or whatever prefix you want as long as it matches the send part of the OR condition

Have the job owned by member of sysadmin

Now they will be able to execute any job with a MyJob prefix in the job name and it will run under a sysadmin account. They will also be able to view job history and only see the jobs they have permissions to.