There are three (3) fixed database roles available under "msdb" database to access SQLServerAgent. Roles are as below...
- SQLAgentOperatorRole: This is the most privileged of the SQL Server Agent fixed database roles. Members can create/delete/modify any job & schedules (own by any user). Also, Members of this role can view properties for operators and proxies, and enumerate available proxies and alerts on the server.
- SQLAgentReaderRole: Members have permission to view the properties of any job & schedules (own by any user). Members cannot change job ownership which is own by others to gain access.
- SQLAgentUserRole: Members have permissions on only local jobs and schedules that they own. They can also view the operators. No permission to view the Proxy node BUT in Job Step they can see to use only.
https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles
Now, let's grant the appropriate role to a user. There are two ways you can do it.
- Using SSMS: Please follow below steps to grant a user to an SQLAgentRole
- Expand the Database Server Node.
- Expand Security Node.
- Select the User where you want to grant access. Right click on it and go to Properties
- In Properties window, navigate "User Mapping" from the left pane.
- From the right pane, select/tick on "msdb" database.
- In the bottom pane of the same window, select appropriate fixed database role [discussed above].
- All set, now click OK and check :)
- Using tSQL: Using the below script you can grant any role to a user...
- Change the user name and role name accordingly
-
USE [msdb]
GO
CREATE USER [MEDAVANTE\testUser]
FOR LOGIN [MEDAVANTE\testUser]
GO
USE [msdb]
GO
ALTER ROLE [SQLAgentReaderRole]
ADD MEMBER [MEDAVANTE\testUser]
GO
 |
| SSMS - Grant SQL Server Agent Role |
Hope this post will help you to grant SQLServerAgent fixed role access to any user.
