As I mentioned in the introductory post, during the Introduction to SQL Server Security session for Pragmatic Work’s Training on the T’s, I received a large number of questions that there wasn’t time to answer. Instead of just a re-cap of all of the questions, instead I’ve opted to put together a post per topic. Hopefully, this will help, not just those that attended the session, but also anyone searching for the same questions later on.
Security question
It’s been a few days, but back to the series and the next question is:
How would handle permissions for people that need full SQL Agent permissions (including being able to edit other peoples jobs) without giving sysadmin rights?
Usually when I get questions about SQL Agent Permissions, I’m not able to give an answer that is satisfactory. In this case, though, there is a role within the MSDB database that covers these permissions. That role is the SQLAgentOperatorRole. Assigning that role provides a user with full permissions to create, edit, and execute jobs created by themselves or others.
For completeness, let’s look at the three roles available in MSDB that are used for SQL Agent permissions. These roles are:
- SQLAgentRole – User can create and manage their own jobs
- SQLAgentReaderRole – Same permissions as SQLAgentRole and the ability to list and review history on multiserver jobs.
- SQLAgentOperatorRole – Same permissions as previous roles, plus the ability to start/stop any job, view alerts, properties, and proxies, and disable or enable any job.
If you are going to be letting your junior DBAs assist in managing SQL Agent jobs, it is important to keep these roles in mind. The last thing you will want is a new (and junior) DBA inadvertently modifying a job when they are just supposed to be reviewing and troubleshooting it.
Summary
Controlling permissions on MSDB for SQL Agent is as important as permissions on any other area of SQL Server. Do you take the time to minimize permissions for users or do you just let everyone with access have full access? Do you think it is important to restrict MSDB and SQL Agent permissions?
