1

I have some instances were some of the BI teams can create their own schduled jobs in the sql agent.

However i also have a couple house keeping jobs i don't want them to interfere with. Can I create a role and specify only people with this role can update the house keeping jobs?

If so how?

3

Based on the information in SQL Server Agent Fixed Database Roles, it appears that non-sysadmin users must be a member of one of the following fixed database roles in msdb to utilize the SQL Agent:

  • SQLAgentUserRole
  • SQLAgentReaderRole
  • SQLAgentOperatorRole

Each of the above fixed database roles progressively allows more authority than the previous one, but with regards to being able to modify jobs, all three indicate that permissions are granted for owned jobs only.

The way I interpret the information in this post is, if you are not a member of the sysadmin fixed role, you only have the permissions to modify jobs that you own, whether that be an individual or a windows group.

You should be able to easily test this theory by creating two or more users, assigning them to one of the above fixed database roles (making sure they are not members of sysadmin), creating individual jobs under each user and then attempting to alter jobs not owned by that user.

I've included some information below about the permissions for each of fixed database roles mentioned above (highlighting mine).

SQLAgentUserRole Permissions

SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs, and job schedules. Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own. They cannot use multiserver jobs (master and target server jobs), and they cannot change job ownership to gain access to jobs that they do not already own. SQLAgentUserRole members can view a list of available proxies only in the Job Step Properties dialog box of SQL Server Management Studio. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of SQLAgentUserRole.

SQLAgentReaderRole Permissions

SQLAgentReaderRole includes all the SQLAgentUserRole permissions as well as permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own. SQLAgentReaderRole members cannot change job ownership to gain access to jobs that they do not already own. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of the SQLAgentReaderRole.

SQLAgentOperatorRole Permissions

SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server.

SQLAgentOperatorRole members have additional permissions on local jobs and schedules. They can execute, stop, or start all local jobs, and they can delete the job history for any local job on the server. They can also enable or disable all local jobs and schedules on the server. To enable or disable local jobs or schedules, members of this role must use the stored procedures sp_update_job and sp_update_schedule. Only the parameters that specify the job or schedule name or identifier and the @enabled parameter can be specified by members of SQLAgentOperatorRole. If they specify any other parameters, execution of these stored procedures fails. SQLAgentOperatorRole members cannot change job ownership to gain access to jobs that they do not already own.

The Jobs, Alerts, Operators, and Proxies nodes in SQL Server Management Studio Object Explorer are visible to members of SQLAgentOperatorRole. Only the Error Logs node is not visible to members of this role.

  • ...the minimum requirement would be to assign the SQLAgentUserRole to the developers, thus allowing them to create and modify their own jobs and not allowing them to modify others. – hot2use Jul 23 '18 at 12:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.