SQL Server Agent roles?

Published on August 27 2015 by LakshmiSaahul,Dhana Royal

What are the differences in the SQL Server Agent roles?

SQLAgentUserRole
Ability to manage Jobs that they own
SQLAgentReaderRole
All of the SQLAgentUserRole rights
The ability to review multiserver jobs, their configurations and histor
SQLAgentOperatorRole

All of the SQLAgentReaderRole rights
The ability to review operators, proxies and alerts
Execute, stop or start all local jobs
Delete the job history for any local job
Enable or disable all local jobs and schedule

What types of users should be granted these rights?

Although production DBAs will have System Administrator rights, the new SQL Server Agent Fixed Database Roles would be considerations for:

Junior DBAs
Developers
Data Warehouse Developers
IT Operations
Management

How do I grant these rights?

The rights are granted at the MSDB database level. To grant rights the follow these steps:

Open SQL Server 2005 Management Studio
Navigate to Databases | System Databases | msdb | Security | Roles | Database Roles
Double click on 1 of the roles to add members to the role
Use the Database Role Properties window to manage the users

Advertising