SQL 2016 allow regular User to create and run specific job(s) ?

Question

Hello, for SQL Server 2016 is there a way to allow a regular Login-User to create and execute only one or specific job(s) (believe they need to be owner of job) but not have any further privileges to avoid changing/running other SQL Agent jobs, Operators, or anything else?

Simply permissions to just create and run.

Thanks in advance.

Answer 1

After searching around I noticed adding them to the SQLAgentUserRole within msdb looks like pretty much does this just for the DB(s) that they own.

In testing around looks like a regular Login-User cannot make any configuration changes to SQL Server Agent which is good (or please let me know if I missed something that they can change which is undesirable).

But when I tried to Stop-Start-Restart services for SQL Server Agent I was able to logged in as a regular Login-User which is not good...is there a way to prevent regular Login-User to not be able to

Answer 2

Please see:
https://learn.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles?view=sql-server-ver15

It describes exactly what can and cannot be done under a role.

Answer 3

Thanks TomPhillips-1744 for your reply with link.

Interesting I just came across this old link that mentions similar what I tested that a regular Login-User when added to the SQLAgentUserRole can Stop-Start-Restart the SQL Agent service.

https://www.sqlservercentral.com/forums/topic/sqlagentreaderrole-enables-a-user-to-startstop-the-agent-service

Wonder if there's a way to still grant regular Login-User SQLAgentUserRole but deny ability to Stop-Start-Restart the SQL Agent service?

Answer 4

Hi techresearch7777777-7743,

The ability to start and stop any service, SQL Server—related or not, is strictly a function of the rights granted to the user's Windows user account. By default, all Windows users who have administrator or power-user permissions can stop, start, and pause services. You can remove the rogue user from the Windows administrator group or create a set of policies that clarify who can and can't stop SQL Server—related services.
Please check Blocking SQL Server Agent Stops and Starts and Deny user to start and stop SQL services which might be helpful.

Regards,
Amelia

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.