Product:
Microsoft SQL Server 2016
Microsoft Windows 2012
Issue:
How read the SQL agent jobs logs without being a sysadmin?
Solution:
Add the user to a AD group and add the AD group as a login in the SQL server. In our example we will instead use SQL login User3.
In SSMS go to Security – Logins – New Login. https://www.guru99.com/sql-server-create-user.html Create your login in SQL.
Go to your MSDB database under – Databases – System Databases – msdb – Security – Users – New User.
Add your User3 there to the msdb database.
Expand Membership and select SQLAgentReaderRole, then click OK.
Then that user can in SSMS see SQL Server Agent, and View History and properties of a SQL Agent Job.
With SQL script:
USE [msdb] exec sp_addrolemember 'SQLAgentReaderRole', 'DomainName\GroupName'
More Information:
https://www.techonthenet.com/sql_server/users/create_login.php
https://www.tutorialsteacher.com/sqlserver/grant-permissions-to-user