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

Document ID : KB000111971
Last Modified Date : 08/10/2018
Show Technical Document Details
Issue:
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://docops.ca.com/ca-unified-infrastructure-management-probes/ga/en/alphabetical-probe-articles/sqlserver-sql-server-monitoring/sqlserver-metrics 


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