APMIA MSSQL Ext - Permission errors with enabling Extra Storage Space Metrics.

Products

• CA Application Performance Management (APM / Wily / Introscope)

Issue/Introduction

APMIA MSSQL Ext - Permission errors with enabling Extra Storage Space Metrics. We enabled Extra storage space metrics and noticed multiple issues with it:

1. Some of the custom queries we added, stopped reporting data when extra storage space metrics were implemented. 

2. We saw permission errors in the logs, few examples are:

[ERROR] [IntroscopeAgent.DBMonitor] com.microsoft.sqlserver.jdbc.SQLServerException: The server principal "DX_APM_SQL_NP" is not able to access the database "model" under the current security context.

Can you also let us know what exact permissions are required for executing the stored procedure that gets all the storage space metrics.

Environment

Release : 22.1

Resolution

Explained error in logs 
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e3f1604f-55c0-420d-8240-77773211e9ad/error-the-server-principal-quotservernamequot-is-not-able-to-access-the-database?forum=sqltools
Customer asked only on permissions and not the error.
Provided the necessary configuration settings that appeared not to be set 
https://techdocs.broadcom.com/us/en/ca-enterprise-software/it-operations-management/dx-apm-agents/SaaS/infrastructure-agent/MS-SQL-Database-Monitoring/SQL-Server-Monitoring-Metrics.html#concept.dita_08d63aa1-31a3-4b2c-8312-a48ee8dc437c_ViewingExtraStorageSpaceMetrics.

Permissions:

create user <USER-NAME> for login <LOGIN-NAME>
GO
use master
GO
grant view server state to <USER-NAME>
GO
grant select on sys.sysprocesses to <USER-NAME>
GO
grant select on sys.dm-os_sys_info to <USER-NAME>
go
grant select on sys.configurations to <USER-NAME>
GO
grant select on sys.dm_db_index_physical_stats to <USER-NAME>
GO
use msdb
GO
grant select on dbo.sysjobhistory to <USER-NAME>
GO
grant select on dbo.syscategories to <USER-NAME>
GO
grant select on dbo.sysjobs to <USER-NAME>
GO