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

Errors: 

Explained error in logs 
Error: The server principal "ServerName" is not able to access the database "model" under the current security context. (Microsoft SQL Server, Error: 916)

Permissions: 
Customer was only concerned about the permissions and not the errors in the log.
Provided the necessary configuration settings and permissions that appeared not to be set 
Viewing Extra Storage Space Metrics

permissions to set:

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