Submit a ticketCall us

AnnouncementsFace your biggest database issues head-on

Our new eCourse helps you navigate SQL Server performance blocks by teaching you how to recognize and deal with the three DBA Disruptors: Performance Hog, Blame Shifter, and Query Blocker. Register today to learn how to defend your environment and fend off menacing disruptions.

Register for your free eCourse.

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

SQL Server permissions for DPA monitoring

Created by Interspire Import, last modified by Jamin Walters on May 03, 2018

Views: 5,058 Votes: 1 Revisions: 30

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

 

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.

 

  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
Last modified

Tags

Classifications

Public