Submit a ticketCall us
Home > Success Center > Database Performance Analyzer (DPA) > SQL Server permissions for DPA monitoring

SQL Server permissions for DPA monitoring

Table of contents
Created by Interspire Import, last modified by Anthony.Rinaldi_ret on Oct 13, 2016

Views: 435 Votes: 0 Revisions: 24

Overview

This article talks about SQL server permissions for DPA monitoring. 

Environment

  • DPA 9.0 and later
  • Ignite 4.3 and later

Detail

SYSADMIN privileges

When you register a SQL Server instance to be monitored by DPA, DPA will set up a monitoring user with SYSADMIN privileges or use an existing SYSADMIN user account. DPA requires a user with SYSADMIN privileges for the initial registration. For Ignite 8.1 and below, SYSADMIN privileges are required to perform Show Plans for SQL statements. SYSADMIN privileges can be removed from the monitoring user after DPA has captured initial data. See the last section in this article for more information.

 

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.

To turn this flag off for a monitored database instance:

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

 

WMI metrics collection

The following statements always run when DPA connects to a non-RDS SQL Server instance:

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

 

Notes:

  • In DPA 9.2 and earlier, these statements enable WMI-based resource metric collection, which is required.
  • In DPA 10.0, WMI-based resource metric collection is no longer required. However, the statements above still run when DPA connects to a non-RDS SQL Server instance.
  • In DPA 10.1 and later, the above statements are only run if the SQL_SERVER_WMI_METRICS_ENABLED advanced option is set to TRUE and DPA connects to a non-RDS SQL Server instance.

 

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. If SYSADMIN privileges are removed, the Show Plans and 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 those actions.

  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. Replace the user CONFIO\confio with your own username:
CREATE LOGIN [CONFIO\confio] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GRANT VIEW ANY DATABASE TO [CONFIO\confio]
 GRANT VIEW SERVER STATE TO [CONFIO\confio]
 GRANT VIEW ANY DEFINITION TO [CONFIO\confio]
 GO

For DPA 10.0 and later:

 USE [master]

--Only run the following commands if the system option
--SQL_SERVER_WMI_METRICS_ENABLED is set to True:

 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]

--

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

For DPA 9.2 and earlier:

 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]

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

For Ignite 8.1 and earlier for plan collection:

USE [master]
 EXEC sp_MSforeachdb '
   USE ?
   CREATE USER [CONFIO\confio] FOR LOGIN [CONFIO\confio] WITH DEFAULT_SCHEMA=db_datareader
 '
 GO

 

Note: If SYSADMIN privileges are taken away from the DPA monitoring user, SolarWinds recommends turning off the Historical Plan Collection feature of DPA because it will receive errors. To turn this feature off:

  1. Click Options > Administration tab > Advanced Options.
  2. Click the DB Instance Options tab and select the SQL Server instance.
  3. Change the value of the PLAN_COLLECTION_ENABLED option to False.
Last modified
10:49, 13 Oct 2016

Tags

Classifications

Public