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',
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
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 -
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
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
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
Thanks for your time
Subscribe and follow us for all the latest information directly on your social feeds: