sqlserver probe: The SELECT permission was denied on the object 'syscategory'

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

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'.

 

Environment

Release : UIM 20.4

sqlserver probe 5.x

Cause

This issue can be caused by

1. insufficient permissions provided to the user used for the connection to the DB

OR

2. Mismatch/corruption of the connection string/information inside the profile. 

 

Resolution

1. insufficient permissions provided to the user used for the connection to the DB

 

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:

master.sys.databases
master.dbo.sysperfinfo
msdb.dbo.sysjobsteps
msdb.dbo.sysjobs
msdb.dbo.syscategories
msdb.dbo.log_shipping_monitor_secondary
msdb.dbo.log_shipping_monitor_primary
msdb.dbo.sysjobhistory
.sys.database_files
.sys.partitions
.sys.allocation_units
.sys.internal_tables
.sys.filegroups

 

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. 

 

 

 

2. The connection name in the profile is wrong

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

Example: 

 

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.