11/19/2018 7:11 PM

3/13/2025 9:03 PM

When you register a SQL Server instance for monitoring with DPA 2020.2 and earlier, 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, as described below.

Note: If you are using DPA 2020.2.1 or later, you can register a SQL Server database instance without a privileged user with the SYSADMIN role. For instructions, see Register a SQL Server instance for DPA monitoring without the sysadmin role.

Limitations when you remove the SYSADMIN role

If you remove the SYSADMIN role from the DPA monitoring user, the following reports will not run:

• Windows Service Not Running - SQL Server
• SQL Server Log has Many Virtual Logs

The following reports require additional grants to the monitoring user:

• SQL Server Error Log Alert.
  To run this alert, provide the following grants to the monitoring user:

  use master;
  GRANT EXECUTE ON xp_enumerrorlogs TO MonUser
  GRANT EXECUTE ON xp_readerrorlog TO MonUser
  GO

• SQL Server Long Running Jobs.
  To run this alert, provide the following grants to the monitoring user:

  use msdb;
  GRANT EXECUTE ON agent_datetime TO MonUser
  GRANT SELECT ON syssessions TO MonUser
  GRANT SELECT ON sysjobactivity TO MonUser
  GO

When DPA connects to the monitored instance, it runs DBCC TRACEON (2861, -1) to turn on SQL Server trace flag 2861. If you remove the SYSADMIN role from the DPA monitoring user, you might need to adjust an option that controls this behavior. See the "SQL Server trace flag 2861" section in the Resolution.

Database Performance Analyzer

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

• Remove SYSADMIN privileges from the DPA monitoring user account
• SQL Server trace flag 2861
• WMI metrics collection

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. (For information about what effect specific privileges have on monitoring, see the "Impact of specific permissions on DPA monitoring" section in the article Register a SQL Server instance for DPA monitoring without the sysadmin role.)

If SYSADMIN privileges are removed, the the Real Time - Kill Sessions features of DPA will no longer work because the DPA monitoring user requires a high level of privileges to perform this action.

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 SQL Server instance.
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:
   1. Run the commands for all versions.
   2. Run the version-specific commands in one of the sections that follow. 

Commands for all versions of DPA

GRANT VIEW ANY DATABASE TO  [CONFIO\confio]
GRANT VIEW SERVER STATE TO  [CONFIO\confio]
GRANT VIEW ANY DEFINITION TO  [CONFIO\confio]
GRANT ALTER ANY EVENT SESSION TO  [CONFIO\confio]
GRANT CONNECT ANY DATABASE TO  [user]
GO 

Commands for DPA 12.0 and later with SQL Server 2014 and later

USE [master]

--Only run the following commands if the system option 
--SQL_SERVER_WMI_METRICS_ENABLED is set to True: 
--USE [master] 
--GRANT EXECUTE ON sys.sp_OADestroy TO [CONFIO\confio] 
--GRANT EXECUTE ON sys.sp_OAGetErrorInfo TO [CONFIO\confio] 
--GRANT EXECUTE ON sys.sp_OACreate TO [CONFIO\confio] 
--GRANT EXECUTE ON sys.sp_OAGetProperty TO [CONFIO\confio] 
--GRANT EXECUTE ON sys.sp_OAMethod TO [CONFIO\confio] 
--GRANT EXECUTE ON sys.sp_OAStop TO [CONFIO\confio] 
--GRANT EXECUTE ON sys.sp_OASetProperty TO [CONFIO\confio] 
--GO 
--Connect to any database supported since SQL Server 2014. 
--On older versions you have to create the user on each database manually. 

USE  [master]
GRANT CONNECT ANY DATABASE TO [CONFIO\confio]
GRANT CREATE TABLE TO [CONFIO\confio]
GO 

USE [msdb]
GRANT SELECT ON msdb.dbo.sysjobs TO [CONFIO\confio]
GRANT SELECT ON msdb.dbo.sysjobhistory TO [CONFIO\confio]
GO

Commands for DPA 12.0 and later with a database older than SQL Server 2014

If you have DPA 12.0 or later but your database is older than SQL Server 2014, you will need to run something similar to the following statement to add the user to each DB if you are removing SYSADMIN.

DECLARE @DPA_User varchar(50) = 'dpa_m'; -- if AD account, set this to DOMAIN\user, e.g. TUL\dpa_m
DECLARE @dbname VARCHAR(50);
DECLARE @SQL NVARCHAR(max);

DECLARE dbs CURSOR LOCAL FAST_FORWARD FOR
    SELECT name
    FROM MASTER.dbo.sysdatabases;

OPEN dbs;
FETCH NEXT FROM dbs INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN <
    SET @SQL = 'use '+@dbname +'; 
    CREATE USER '+@DPA_User+' FOR LOGIN '+@DPA_User+';
        EXECUTE sp_addrolemember N''db_datareader'', '+@DPA_User+';'
 
    EXECUTE sp_executesql @SQL;
    FETCH NEXT FROM dbs INTO @dbname;
END;
CLOSE dbs;
DEALLOCATE dbs;

Commands for DPA 10.0 through DPA 11.1

--Only run the following commands if the system option 
--SQL_SERVER_WMI_METRICS_ENABLED is set to True: 
--USE [master] 
--GRANT EXECUTE ON sys.sp_OADestroy TO [CONFIO\confio] 
--GRANT EXECUTE ON sys.sp_OAGetErrorInfo TO [CONFIO\confio] 
--GRANT EXECUTE ON sys.sp_OACreate TO [CONFIO\confio] 
--GRANT EXECUTE ON sys.sp_OAGetProperty TO [CONFIO\confio] 
--GRANT EXECUTE ON sys.sp_OAMethod TO [CONFIO\confio] 
--GRANT EXECUTE ON sys.sp_OAStop TO [CONFIO\confio] 
--GRANT EXECUTE ON sys.sp_OASetProperty TO [CONFIO\confio] 
--GO 
--
USE [master]
GRANT CREATE TABLE TO [CONFIO\confio]
GO

USE [msdb]
GRANT SELECT ON  msdb.dbo.sysjobs TO [CONFIO\confio]
GRANT SELECT ON msdb.dbo.sysjobhistory TO [CONFIO\confio]
GO

SQL Server trace flag 2861

When DPA connects to the monitored instance, it runs DBCC TRACEON (2861, -1) to turn on SQL Server trace flag 2861. DPA turns this flag on to get text for quick-running SQL statements. For more information, see this article (content provided by Thomas LaRock, available at https://logicalread.com, obtained on Dec. 13, 2018).

DPA has a setting that relates to this flag. The options for this setting are ON, OFF and MANUAL. 
With the sysadmin role removed DPA is not able to set this flag to on or off and permission errors are seen in the logs on monitor startup. It is recommended to set the settings to Manual. 

1. Click Options > Administration tab > Advanced Options.
2. Click the DB Instance Options tab and select the database instance.
3. Select Support Options in the upper right corner.
4. Change the value of the DBCC option to MANUAL.
5. Restart DPA.

WMI metrics collection

In some situations, DPA runs the statements below to enable WMI-based resource metric collection:

• In DPA 10.1 and later, the statements run only if both of the following conditions are met:
  • The SQL_SERVER_WMI_METRICS_ENABLED advanced option is set to TRUE.
  • DPA connects to a non-RDS SQL Server instance.
• In DPA 10.0, WMI-based resource metric collection not required, but the statements run when DPA connects to a non-RDS SQL Server instance.
• In DPA 9.2, WMI-based resource metric collection is required. These statements always run.

Statements:

EXEC sp_configure "show advanced options", 1
RECONFIGURE WITH OVERRIDE

EXEC sp_configure "ole automation procedures", 1
RECONFIGURE WITH OVERRIDE

DECLARE @WmiServiceLocator int, @WmiService int
EXEC sp_OACreate 'WbemScripting.SWbemLocator', @WmiServiceLocator output, 5
EXEC sp_OAMethod @WmiServiceLocator, 'ConnectServer', @WmiService output, '.', 'root\\\\cimv2'
EXEC sp_OADestroy @WmiService
EXEC sp_OADestroy @WmiServiceLocator