Permissions on SQL Server Agent

Question

Hi all,

I have a requirement that users can execute sql-agent jobs but should not be able to

a.)    Disable the job

b.)    Change any other setting within the job like schedule and notification.

When I assign the user to the SQLAgentOperatorRole it of course allows running the job, which is what I want but it does not prevent the user from disabling a jobschedule or disabling the whole job which is what I don’t want…

 If I take away the SQLAgentOperatorRole from this user and only have SQLAgentReaderRole and SQLAgentUserRole enabled then I can’t execute the job anymore. It seems that the standard roles do not allow this requirement.

There is a stored proc. called exec msdb.dbo.sp_start_job N'JobwithServeralSteps'

and I figured when I grant execute permission for this account on that stored proc then it might execute including not being possible for the user to change the schedule or disable the job. But it states …see below

Msg 14393, Level 16, State 1, Procedure sp_start_job, Line 42

Only owner of a job or members of role sysadmin or SQLAgentOperatorRole can start and stop the job.

 

How can a user only execute the job but can’t change any of the settings mentioned above?

Any hint much appreciated.

Thx

L.

Answer 1

All SQL Server Agent fixed database roles allow you to execute a job, however both the SQLAgentReaderRole and the SQLAgentUserRole roles allow you to only execute the jobs you own (but they also allow you to edit them). Since this is built-in functionality, I am not sure there's anything you can do about this, since the only way they would be able to execute a job with a different owner is by having access through the SQLAgentOperatorRole role.

The only alternative I see is to manage this via a front-end application with it's own built-in security model.

For more information, please take a look at SQL Server Agent Fixed Database Roles - http://msdn.microsoft.com/en-us/library/ms188283.aspx.

---

MG.-
Mariano Gomez, MIS, MCITP, PMP
IntellPartners, LLC.
http://www.intellpartners.com/
Blog http://dynamicsgpblogster.blogspot.com/

Answer 2

Have a look at my article about granting permissions to stored procedures:
http://www.sommarskog.se/grantperm.html

The article includes several solutions how you can let a user start a job without being member in an msdb role themselves.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

Thx Erland for the information provided. I'll give it try, however I have to say this should be better supported out of the box. Just one more inbuld agent role that allows only for the execution of a job instead also including certain job edit permissions would swing it.

Some of our folks in operations just disable jobs without real authorization and forget to enable them again. Wonder if I can catch this via an audit or Policy-Based Management instead of approaching it via security. Will have a look as well. Your thoughts?

I searched the web if SQL-2012 supports this out of the box but didn't find any helpful information yet. Anyone else running into this and already applied the approaches Erland lays out in the link above? Please let us know.

Thx again

L. 

Answer 4

Thx Mariano for looking into this question. Let's see if other users have run into this...and how they approached it..

Thx again

L.

Answer 5

Thx Erland for the information provided. I'll give it try, however I have to say this should be better supported out of the box.

If you feel that way, please go to
http://connect.microsoft.com/sqlserver/feedback
and submit a suggestion. Tip: first search to see if it has been suggested before.

I don't know if there any improvements in this area in SQL 2012.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 6

Thx Erland, will do.

Thx

L.

Answer 7

I believe that you can deny execute on the sps that make the changes.  sp_update_job, sp_update_jobstep, sp_update_jobschedule etc.

Before the roles were introduced we gave people permissions by denying and granting as needed.

The roles we have now in msdb are more granular then they used to be but as you state they could be even better.

 

Best Regards,

 

Susan

 

---

The light at the end of the tunnel may be you.

Answer 8

Hi Susan,

I see your point and it makes perfect sense.. by denying the permissions on sp_update_job I can cover both of my requirements.

My point was more like what are the guidlines MS uses to design the inbuild roles...

So currently if a user has too much rights with the inbuild agent roles we (MS-client) reduces them (in a different dialog by the way)with denying specific rights...which were included in the inbuild roles...hmmmI know that is how security works and a software company can't know how each client wants the rights layout for a specific position to have it all in build-in roles but this ability to grant permissions on very granular features/levels comes with a high price in terms of maintainability....espically in high turn over areas...each new dba needs to have a good understanding of this area (what deny takes away which feature etc.) and this is very often not the case..and test/time intensive to find out...

Tes, guess SQL-Server security alone is an area to specialize in...:)

Thx

L.