Grant Access SQL Server Agent using tSQL/SSMS, SQL Server

When you need to grant the user to access (read/manage) "SQL Server Agent", you can do it using either using SSMS or tSQL.

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.

For more details about the Roles, please visit below link ...
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.

1. Using SSMS: Please follow below steps to grant a user to an SQLAgentRole



SSMS - Grant SQL Server Agent Role

1. Expand the Database Server Node.
2. Expand Security Node.
3. Select the User where you want to grant access. Right click on it and go to Properties
4. In Properties window, navigate "User Mapping" from the left pane.
5. From the right pane, select/tick on "msdb" database.
6. In the bottom pane of the same window, select appropriate fixed database role [discussed above].
7. All set, now click OK and check :)
2. Using tSQL: Using the below script you can grant any role to a user...
1. Change the user name and role name accordingly

2. USE [msdb] GO CREATE USER [MEDAVANTE\testUser] FOR LOGIN [MEDAVANTE\testUser] GO USE [msdb] GO ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [MEDAVANTE\testUser] GO

Hope this post will help you to grant SQLServerAgent fixed role access to any user.