UIM sqlserver probe user does not have permission to perform this action for checkpoints

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

Using sqlserver probe and facing permission error for checkpoints fg_freeSpace_with_avail_disk and logfile_usage_with_avail_disk checkpoints

Example

Profile: xxxxx/logfile_usage_with_avail_disk/Run Query - DB Provider: Code=0x0x80040e09 Source=Microsoft OLE DB Provider for ODBC Drivers Description=[Microsoft][ODBC SQL Server Driver][SQL Server]The EXECUTE permission was denied on the object 'xp_fixeddrives', database 'xxxx', schema 'sys'.

Environment

UIM 9.X and earlier
sqlserver 5.4x

Resolution

Permission denied alarms for the fg_freeSpace_with_avail_disk and logfile_usage_with_avail_disk checkpoints are due to the fact these are being monitored using a non-sysadmin user in
sqlserver probe . These two checkpoints both require the use of the xp_fixeddrives Stored Procedure (SP).
This SP requires a sysadmin SQL role to function correctly as it requires access to the OS to check the drives. 

This is mentioned also in the sqlserver probe documentation 

https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/it-operations-management/ca-unified-infrastructure-management-probes/GA/alphabetical-probe-articles/sqlserver-sql-server-monitoring/sqlserver-sql-server-monitoring-release-notes.html


QOS_SQL_Server_logfile_usage_with_avail_disk    Percent    
Monitors free space in the database log files after considering the available disk size. 
Note: You require System Administrator privileges on the database server to execute this checkpoint. 

QOS_SQL_Server_fg_freeSpace_with_avail_disk    Percent    
Monitors the amount of free disk space in database file groups in %. 
Free space for file groups (with auto growth enabled) is calculated after considering the available disk size on which the file group is located. 

Notes: 

A single query is executed for all the databases of the SQL server. If any of the database fails to execute the query, the query is considered as failed for the SQL server. 
You require System Administrator privileges on the database server to execute this checkpoint. 

UIM cannot offer a work around for this - we did not design this SP and it is implemented as is by Microsoft. The probe simply uses the SP to deliver freespace checkpoints. 

Can point out a potentially helpful thread on StackExchange. 

The thread can be found here: 

http://serverfault.com/questions/82314/how-can-i-give-a-sql-server-user-permission-to-run-one-stored-procedure-and-noth