by Aldo Bucossi

By default, MS-SQL Server Agent attempts to execute commands and queries for functionality that may
be not present or configured (e.g job and transaction history).
In environments using Guardium, this results in error flags because the account used by
the agent does not have the required permissions to successfully execute these queries.

The errors looks like:

The %ls permission was denied on the object '%.*ls', database '%.*ls',  
schema '%.*ls'.  
User '%.*ls' does not have permission to run DBCC %ls for database '%.*ls'.

There are two possible options to get rid of those unwanted alerts.

1) Grant the necessary permissions for the account used by the SQL Server Agent.
2) Disable data collection for the attribute groups returning errors, specifically Database Detail and Job Detail.

Option 1: Grant the necessary permissions.

If you decide to provide the necessary permissions, first of all be sure you followed all the required steps
from:

https://www.ibm.com/support/knowledgecenter/SSDKXQ_6.3.1/com.ibm. itcamms.doc_6.3.1/sql/grantpermissionwin.html

If "sysjobhistory" exception is also present, then you can grant "db_datareader" and "SQLAgentUserRole","SQLAgentreaderRole",  
"SQLAgentOperatorRole" on MSDB Database to the SQL Server Account used by SQL Server Agent and verify if this is enough
to fix the exception.
   
To fix errors concerning "dbcc opentran" and "dbcc proccache", it requires membership in the  
"sysadmin" fixed server role or the "db_owner" fixed database role to collect the data.

 MSDN Link -  
https://msdn.microsoft.com/en-us/library/ms184310.aspx

In case you cannot or don't want to grant anything more, you can consider option 2.

Option 2: Disable Data collection for the attribute groups generating the errors

In order to prevent the execution of the failing queries, you can disable the collection of Database Detail and Job Detail  
attribute groups.

   
Database Detail attribute group is an interval based attribute group and can be disabled by following the below steps :  
   
1. Open the MTEMS window.  
2. Stop The Agent.  
3. In the MTEMS window, under the Task/SubSystem column, right-click  Template, and then select Configure Using Defaults.  
4. In the Configure Database Agents window, from the Servers to Monitor list, select the database server for which you want to disable the  
collection, and then click on Edit Button.
5. In the Database Server Properties window -> under setting panel-> go  
to the Extended Params and type "@KOQDBD'" value in the text box.  
6. Start the agent  
   
   
Job Detail attribute group is instead interval based so the data collection for this happens after every 3 min  
In order to disable data collection for Job Detail Attribute group, you can perform the following steps:
   
1. Stop The Agent.  
2. Go to "candle_home\TMAITM6_x64"(for 64 bit agent) or "candle_home\TMAITM6"(for 32 bit agent) and launch file named  
"KOQCtlEditUtility.exe"  
3. Go to koqcoll tab on right hand side all attribute groups of MS SQL Agent are listed.  
4. Select KOQJOBD and KOQJOBS click on Disable Button and then click on Apply and close the window.  
5. Start the agent