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?
Strate SQL 
You are incorrect in stating: “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.”
That role does not allow users to edit jobs created by others. You need to grant SA in order to modify jobs owned by others unless you create custom SPs to override the built in agent SPs and modify everything using script in stead of the GUI, as this blog points out http://borishristov.com/blog/modifying-not-owned-sql-agent-jobs-without-being-a-sysadmin/
LikeLike
Good stuff, this may be part and parcel of my effort to consolidate some single-purpose databases to a single instance. Someone who required sysadmin rights on their own instance may be able to be a db_owner in their database and ‘SQLAgentRole’ in msdb on a shared instance. In my environment we would be very cautious before letting various staff have essentially full permissions to each others jobs by giving out ‘SQLAgentOperatorRole’.
One barrier I see is that a SQL Agent job seems to only accept single user logins as the owner. Active Directory groups don’t seem to be available for selection. As a consequence, the jobs would have to be tied to a single user account, not shared amongst a group of people. This creates a sysadmin task to manage the job ownership on a more regular basis. Am I missing something that would allow a job to have multiple owners, through a group or some other means?
LikeLike