Submit a ticketCall us

AnnouncementsAre You “Flying Blind?”

When it comes to your complex IT infrastructure, you want to ensure you have a good grasp of what’s going on to avoid any fire drills that result from guesswork. Read our white paper to learn how proactively monitoring your IT environment can help your organization while giving you peace of mind.

Get your free white paper.

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 Melanie Boyd on Mar 01, 2019

Views: 6,325 Votes: 2 Revisions: 37

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:
    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 [CONFIO\confio]
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

Commands 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]
GO

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

 

Last modified

Tags

Classifications

Public