Submit a ticketCall us

Webinar: Web Help Desk for HR, Facilities and Accounting Departments
This webinar will focus on use cases for HR, Facilities and Accounting.

Having a unified ticketing and asset management system for all the departments in your company can provide end-users with a seamless experience and make things easier for your IT team. Yet, with different business tasks and objectives, many departments don’t fully understand the capabilities of Web Help Desk and how the software can be customized for effective use in their departments.
Register Now.

Home > Success Center > Database Performance Analyzer (DPA) > SQL Server permissions for DPA monitoring

SQL Server permissions for DPA monitoring

Created by Interspire Import, last modified by Melanie Boyd on Sep 19, 2017

Views: 783 Votes: 0 Revisions: 26

Overview

This article talks about SQL server permissions for DPA monitoring. 

Environment

  • DPA 9.2 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. 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, 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 to the DPA monitoring user account:
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]
 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:

 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

Recommended: Turn off Historical Plan Collection

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
08:40, 19 Sep 2017

Tags

Classifications

Public