UIM sqlserver probe - MS SQL Server permissions error - The user does not have permission to perform this action.

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)
• Unified Infrastructure Management for Mainframe
• DX SaaS

Issue/Introduction

We are getting an access privilege error for one of the sql servers in our environment. We are giving the same access across all servers and it is working fine. Could you please tell us why we are getting this issue only for this server?

ERROR Message:

Profile: mssqlserver/buf_cachehit_ratio/Run Query - DB Provider: Code=0x0x80040e14 Source=Microsoft OLE DB Provider for ODBC Drivers Description=[Microsoft][ODBC SQL Server Driver][SQL Server]The user does not have permission to perform this action.

Environment

Release : 20.3

Component : UIM - SQLSERVER

Robot version: 7.97HF9

SQL Server version:5.42-T2, 5.45

MS SQL Server 2012 or higher

Resolution

In general, the best practice to ensure that the probe can run without any access/permission issues is to use the "sa" user or an sa-equivalent user.

If this is not possible, then at minimum, the given user configured for the probe (database user Name), needs sufficient access to read the following tables, here is an example of the commands to GRANT access for the user which MUST be run using the MS SQL Server Studio, so you can see the Messages (results) and ensure the commands completed successfully.

DO NOT run the SQL statements in the SLM query window.

-- For SQL Server v11 through v15 (2012 or higher)

USE MASTER;
GRANT VIEW SERVER STATE TO <DB user Name>
GRANT SELECT ON OBJECT::master.sys.databases TO <DB user Name>
GRANT SELECT ON OBJECT::master.dbo.sysperfinfo TO <DB user Name>

USE MSDB;
GRANT SELECT ON OBJECT::msdb.dbo.sysjobsteps TO <DB user Name>
GRANT SELECT ON OBJECT::msdb.dbo.sysjobs TO <DB user Name>
GRANT SELECT ON OBJECT::msdb.dbo.syscategories TO <DB user Name>
GRANT SELECT ON OBJECT::msdb.dbo.log_shipping_monitor_secondary TO <DB user Name>
GRANT SELECT ON OBJECT::msdb.dbo.log_shipping_monitor_primary TO <DB user Name>
GRANT SELECT ON OBJECT::msdb.dbo.sysjobhistory TO <DB user Name>

GRANT SELECT ON OBJECT::.sys.database_files TO <DB user Name>
GRANT SELECT ON OBJECT::.sys.partitions TO <DB user Name>
GRANT SELECT ON OBJECT::.sys.allocation_units TO <DB user Name>
GRANT SELECT ON OBJECT::.sys.internal_tables TO <DB user Name>
GRANT SELECT ON OBJECT::.sys.filegroups TO <DB user Name>
--End of Script for SQL Server v11

Additional Information

Please make sure you are running the latest GA version of the sqlserver probe which is currently v5.45.

http://support.nimsoft.com/Files/Archive/00089/sqlserver-5.45.zip

Please download and deploy it and then retest.

Also, please double-check this url to review all of the sqlserver probe access prerequisites.

Access Prerequisites