In one server, sqlserver probe failed to check the job failure and generates the below error.
Profile: Profile_SQL_Connection/agent_job_failure/Run Query - DB Provider: Code=0x0x80040e09 Source=Microsoft OLE DB Provider for ODBC Drivers Description=[Microsoft][ODBC SQL Server Driver][SQL Server]The SELECT permission was denied on the object 'syscategory' , database 'msdb', schema 'dbo'.
Release : UIM 20.4
sqlserver probe 5.x
This issue can be caused by
1. insufficient permissions provided to the user used for the connection to the DB
2. Mismatch/corruption of the connection string/information inside the profile.
The error: the SELECT permission was denied on the object 'syscateg
This problem raises when the affected user has deny privileges. In this case to the table "syscategories"
This error will be seen when the user you have created does not have sufficient privileges to access specific database objects (tables, stored procedures, functions) in the database.
The agent_job_failure query is the following: Queries used by the sqlserver Probe in UIM (broadcom.com)
SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select h.job_id as job_id, j.name as job_name, c.name category_name,
dateadd(hh,run_time/10000,dateadd(mi,run_time%10000/100,dateadd(ss,run_time%100, convert(datetime,convert(nvarchar(8),run_date),112)))) as rundate,
datediff(minute, dateadd(hh,run_time/10000, dateadd(mi,run_time%10000/100, dateadd(ss,run_time%100, convert(datetime,convert(nvarchar(8),run_date),112)))), getdate()) as elapsed_time
from msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
inner join msdb.dbo.syscategories c on j.category_id = c.category_id
where c.category_class = 1 and h.run_status <> 1 and h.step_id = 0 and
dateadd(hh,run_time/10000, dateadd(mi,run_time%10000/100, dateadd(ss,run_time%100, convert(datetime,convert(nvarchar(8),run_date),112)))) > dateadd(ss,-60*60*36,getdate())
order by elapsed_time
Please use the query in SQL Management Studio with the same user that is used in the probe and you should probably see the same permission error.
Please ask your DBA to gran access to the system tables required in the DB.
System Tables (Transact-SQL) - SQL Server | Microsoft Learn
Additional Troubleshooting steps:
Example of a user without enough permissions, error created:
Please engage your DBA and add db_owner for the msdb system table which is failing.
If you cannot provide db_owner, as mentioned in the Documentation, you will need to grant access to those specific tables.
Please refer to the documentation. --> sqlserver (SQL Server Monitoring) Release Notes (broadcom.com)
The user is mapped for the required permission. User mapping is required for the following tables:
By default, the listed database tables are present. If you add a user without the db_owner and sysadmin permissions, then, you must also add the database tables that are associated with that user. Adding associated database tables enable you to generate alarms for all the databases including the default ones.
For this specific query that we are discussing, I was able to run it granting the following permissoins:
GRANT SELECT ON dbo.syscategories TO "user"
GRANT SELECT ON dbo.sysjobs TO "user"
GRANT SELECT ON dbo.sysjobhistory TO "user"
So without db_owner but with the above grants the query was successful. But you might want to GRANT all other tables to avoid errors with other tables.
Another scenario that can cause this issue is a bad connection in the profile.
Check if the connection visible in the profile or in the configuration file matches the connections available in the "Connections" tab.
If there is a mismatch, this issue will occur
If we look into the connections tab we can see that the connection in the profile does NOT match the connection name from the connections tab::
Use Raw configure to update the profile.
Save restart the probe and check if you are still seeing the same errors.