11/13/2020 8:58 PM

7/23/2025 5:35 PM

When you register an ASMI for monitoring with DPA, DPA requires a privileged user with the SYSADMIN role for the initial registration. You can remove the SYSADMIN role from the DPA monitoring user after DPA has captured initial data with the mandatory permissions and roles:

Permissions:

• VIEW SERVER STATE (mandatory)

• VIEW ANY DATABASE (optional)
• VIEW ANY DEFINITION (optional)
• CONNECT ANY DATABASE (optional)
• ALTER ANY EVENT SESSION (optional)

Roles:

• processadmin (optional)

Database Performance Analyzer

Remove SYSADMIN privileges from the DPA monitoring user account

After DPA has been started and has captured some initial data, it is possible to remove SYSADMIN privileges from the DPA monitoring user. However, you will need to give specific privileges to the underlying objects that DPA needs to access. 

Note: The permissions below are only valid if the default SQL Server permissions for system roles such as [public] have not been altered with items revoked. If default system roles are altered DPA support cannot help with finding all items that are assumed to be allowed.

To remove privileges, complete the following steps:

1. Stop the DPA monitor for the ASMI.
2. Remove the SYSADMIN server role from the DPA monitoring user.
3. Run the following commands to grant specific privileges to the DPA monitoring user account.

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

--grant privileges to the DPA monitoring user
USE [master]
GRANT VIEW SERVER STATE TO [dpa_mon_user]
GRANT VIEW ANY DATABASE TO [dpa_mon_user]
GRANT VIEW ANY DEFINITION TO [dpa_mon_user]
GRANT ALTER ANY EVENT SESSION TO [dpa_mon_user]
GRANT CONNECT ANY DATABASE TO [dpa_mon_user]

--assign processadmin role to allow kill session
ALTER SERVER ROLE [processadmin] ADD MEMBER [dpa_mon_user]

--grant privileges to user on master database
USE [master]
CREATE USER [dpa_mon_user] FOR LOGIN [dpa_mon_user]
GRANT EXECUTE ON OBJECT::xp_enumerrorlogs TO [dpa_mon_user]
GRANT EXECUTE ON OBJECT::xp_readerrorlog TO [dpa_mon_user]
 
--grant privileges to user on msdb database
USE [msdb]
CREATE USER [dpa_mon_user] FOR LOGIN [dpa_mon_user]
GRANT SELECT ON msdb.dbo.sysjobs TO [dpa_mon_user]
GRANT SELECT ON msdb.dbo.sysjobhistory TO [dpa_mon_user]
GRANT SELECT ON msdb.dbo.syssessions TO [dpa_mon_user]
GRANT SELECT ON msdb.dbo.sysjobactivity TO [dpa_mon_user]
GRANT EXECUTE ON msdb.dbo.agent_datetime TO [dpa_mon_user]

Impact of specific permissions on DPA monitoring

DPA validates that the monitoring login and user has the following permissions.

Permissions granted to login

Unless otherwise stated, the permissions are optional. (If optional permissions are missing, basic monitoring is possible but specific data or functionality is not available.)

Permission	Effect on DPA monitoring
VIEW SERVER STATE (mandatory)	Required to access the Dynamic Management Views used for polling. Without it, DPA cannot monitor the instance.
VIEW ANY DATABASE	Required to see any database. Without it, DPA can monitor only master, tempdb, and any databases that the DPA monitoring user owns.
VIEW ANY DEFINITION	Required to access the definitions of tables, indexes, and other database objects. Without it, Storage I/O data and Table Tuning Advisor current definitions are not available.
CONNECT ANY DATABASE	Required to connect to any database. Without it: Table Tuning Advisors cannot include current data, table size, and table churn.  ‘Database Freespace’ and ‘Transaction Log Freespace’ alerts will not function for all databases.
ALTER ANY EVENT SESSION	Required to access Extended Event Sessions used for Deadlock polling. Without it, deadlock polling does not function.

Role membership

This role is optional.

Role	Description
processadmin	Required to kill sessions. Without it, the Kill Session option in the Real Time Sessions view does not work.

Object permissions granted to the DPA monitoring user

All object permissions are optional. 

Database	Object	Object type	Permission	Effect
master	xp_enumerrorlogs	stored procedure	EXECUTE	Required for the SQL Server Error Log Alert.
master	xp_readerrorlog	stored procedure	EXECUTE
msdb	sysjobs	table	SELECT	Required for Long Running Jobs and Job Failure alerts.
msdb	sysjobhistory	table	SELECT
msdb	syssessions	table	SELECT	Required for the Long Running Jobs alert.
msdb	sysjobactivity	table	SELECT
msdb	agent_datetime	function	EXECUTE

Limitations of not granting the sysadmin role to the DPA monitoring user

Some of the permissions that DPA uses can only be granted by granting the sysadmin role to the DPA monitoring user. Without the sysadmin role, the 'SQL Server Log has Many Virtual Logs' alert does not work.