Applications Systems

Register a SQL Server instance for DPA monitoring without entering credentials of a user with the sysadmin role

Register a SQL Server database instance without entering the credentials of a privileged user that has been granted the sysadmin role.

First published date

8/18/2020 10:06 PM

Last published date

9/29/2025 10:43 PM

Overview

In DPA 2020.2 and earlier versions, registering a SQL Server database instance for monitoring required that you provide the credentials of a privileged user with the sysadmin role. During registration, the privileged user either creates the DPA monitoring user or grants the required privileges to an existing user that you designate as the DPA monitoring user.

With DPA 2020.2.1 and later, you can register a self-managed SQL Server instance without a privileged user. (For earlier DPA versions, the sysadmin role is required for registration. See After registering a SQL Server instance, remove the SYSADMIN role from the DPA monitoring user to remove the sysadmin role from the DPA monitoring user.)

In DPA 2020.2.1 through 2021.1.x, you can use mass registration or the DPA management API to register a SQL Server database instance without a privileged user. (If you register a database instance through the UI wizard, a privileged user is required.) See the following sections for instructions.

In DPA 2021.3 and later, you can use mass registration, the API, or a UI wizard to register a SQL Server database without a privileged user:

To use a wizard, see Register a SQL Server database instance in the DPA Administrator Guide.
To use mass registration or the API, see the following sections.

Product section

Database Performance Analyzer

Resolution

-- 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.

See the following sections:

How to register a SQL Server database instance without a privileged user
Monitoring user validation
Impact of specific permissions on DPA monitoring
Limitations of not granting the sysadmin role to the DPA monitoring user

How to register a SQL Server database instance without entering the credentials of a privileged user

Step 1: Create the monitoring user with the necessary permissions

To avoid entering the credentials of a privileged user, you must create the monitoring user yourself. You can create the user in either of the following ways:

To avoid the limitations described in the last section of this article, create a user manually and grant the sysadmin role to that user.
If you do not want the DPA monitoring user to have the sysadmin role, complete the following steps to run a script that creates the monitoring user and assigns the permissions needed for monitoring without the sysadmin role. The majority of the permissions are optional. DPA can monitor without them, but the monitoring will be limited. Limitations are described in the following sections.

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

To create the monitoring user that does not have the sysadmin role:

Copy one of the following scripts to a file:

For a SQL Server 2014 or later instance, copy CreateMonUserSqlServer2014orLater.sql
For a SQL Server 2012 instance, copy CreateMonUserSqlServer2012.sql

(Optional) If you want to grant the monitoring user rights to collect WMI metrics, also copy the following script. Note: DPA collects WMI metrics only if the advanced option SQL_SERVER_WMI_METRICS_ENABLED is set to TRUE and DPA connects to a non-RDS SQL Server instance.

--grant access to OLE Automation used by WMI metrics in DPA if advanced option SQL_SERVER_WMI_METRICS_ENABLED is enabled
USE [master] 
GRANT EXECUTE ON sys.sp_OACreate TO [dpa_mon_user] 
GRANT EXECUTE ON sys.sp_OADestroy TO [dpa_mon_user] 
GRANT EXECUTE ON sys.sp_OAMethod TO [dpa_mon_user] 
GRANT EXECUTE ON sys.sp_OAStop TO [dpa_mon_user]
GRANT EXECUTE ON sys.sp_OAGetErrorInfo TO [dpa_mon_user] 
GRANT EXECUTE ON sys.sp_OAGetProperty TO [dpa_mon_user] 
GRANT EXECUTE ON sys.sp_OASetProperty TO [dpa_mon_user]

Edit the script(s) to update the user name and password values.
Connect to the SQL Server database instance and run the script(s). To ensure that the connected user has all the privileges needed to create the monitoring user, SolarWinds recommends connecting as sysadmin to run the script(s).

Step 2: Register the database instance without specifying a privileged user

If you use DPA 2020.2.1 through 2021.1.x, use mass registration or the DPA management API as described below.

If you use DPA 2021.3 and later, you can use mass registration or the API as described below. Or you can use a wizard. To use a wizard, see Register a SQL Server database instance in the DPA Administrator Guide.

When you use mass registration or the DPA API to register the database instance, omit the privileged user in the registration details.

The following example is a segment of a JSON script that calls the DPA API to register a SQL Server database instance without specifying a privileged user. Change the values to reflect your monitored database and the credentials of the monitoring user created in the previous step.
```
{
  "databaseType": "SQLSERVER",
  "serverName": "127.0.0.1",
  "port": 1433,
  "monitoringUser": "dpa_mon_user",
  "monitoringUserPassword": "Pa$$word1",
  "displayName": "DatabaseName"
}
```
The following shows an example of a SQL Server mass registration spreadsheet used to register a database instance without specifying a privileged user. If you use the SQL Server mass registration spreadsheet:
- Leave the SysAdmin User and SysAdmin Password columns empty.
- In the Create Monitoring User(Y/N) column, enter N.
- In the Monitoring User and Monitoring User Password columns, enter the credentials of the monitoring user created in the previous step.
The following shows an example of a spreadsheet for multiple database types used to register a SQL Server database instance without specifying a privileged user. If you use the spreadsheet for multiple database types:
- Leave the Privileged User and Privileged User Password columns empty.
- In the Create Monitoring User (Y/N) column, enter N.
- In the Monitoring User and Monitoring User Password columns, enter the credentials of the monitoring user created in the previous step.

Monitoring user validation

During the registration, DPA validates that the monitoring user has the required permissions. By default, the registration fails if any mandatory permission is missing and displays a warning if optional permissions are missing. The same validations are also done when database connection details are updated.

This behavior can be overridden by setting the advanced option MONITOR_VALIDATION_OVERRIDE to TRUE. This allows the registration to pass regardless of which permissions are missing. All permissions are treated as optional and DPA displays a warning.

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	For SQL Server 2014 and later versions, required to connect to any database on the server. (For SQL Server 2012, this is replaced by creating the monitoring user on each database and granting the db_datareader role.) 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	Required for the SQL Server Error Log Alert.
master	xp_logininfo	stored procedure	EXECUTE	Required to check sysadmin role membership when using Windows authentication.
master	sys.sp_OACreate	stored procedure	EXECUTE	Required to collect WMI metrics. Validated only if the advanced option SQL_SERVER_WMI_ METRICS_ENABLED is set to TRUE.
master	sys.sp_OADestroy	stored procedure	EXECUTE
master	sys.sp_OAMethod	stored procedure	EXECUTE
master	sys.sp_OAStop	stored procedure	EXECUTE
master	sys.sp_OAGetErrorInfo	stored procedure	EXECUTE
master	sys.sp_OAGetProperty	stored procedure	EXECUTE
master	sys.sp_OASetProperty	stored procedure	EXECUTE
msdb	sysjobs	table	SELECT	Required for Long Running Jobs and Job Failure alerts.
msdb	sysjobhistory	table	SELECT	Required for Long Running Jobs and Job Failure alerts.
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:

DPA cannot collect near-zero or zero cost plans.
The 'SQL Server Log has Many Virtual Logs' alert does not work.
The 'Windows Service Not Running - SQL Server' alert does not work.

Disclaimer: Please note, any content posted herein is provided as a suggestion or recommendation to you for your internal use. This is not part of the SolarWinds software or documentation that you purchased from SolarWinds, and the information set forth herein may come from third parties. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment. You elect to use third-party content at your own risk, and you will be solely responsible for the incorporation of the same if any.