Submit a ticketCall us

Looking to compare latest NPM features with previous versions of NPM?
The NPM new feature summary offers a comparison of new features and improvements offered with this release.

 

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

Azure SQL Database permissions for DPA monitoring

Updated February 14, 2017

Overview

This article talks about Azure SQL database permissions for DPA monitoring.

Environment

  • DPA 11.0 and later

Detail

DB_OWNER privileges

When you register an Azure SQL Server database instance to be monitored by DPA, DPA will set up a monitoring user with DB_OWNER privileges, or use an existing DB_OWNER user account. DPA requires a user with DB_OWNER privileges for the initial registration. For DPA 11.0 and later, DB_OWNER privileges are required to initialize Extended Event Sessions for the Deadlocks feature. Continue reading for more information.

Remove DB_OWNER privileges from the DPA monitoring user account

After you start DPA and it captures some initial data, you can remove DB_OWNER privileges from the DPA monitoring user. However, you must give specific privileges to the underlying objects that DPA must access. If you remove DB_OWNER privileges, most of the features in DPA will no longer work, because the DPA monitoring user requires a higher level of privileges to perform those actions. See the sections below for the minimal permission requirement, and for permissions required to make all DPA features work.

Remove DB_OWNER privileges

Run the following command to remove the monitoring user from the DB_OWNER role. Replace MONITORING_USER_NAME with the name of your monitoring user.

alter role DB_OWNER drop member MONITORING_USER_NAME

Grant minimal permissions to the existing user

  • Permission needed: VIEW DATABASE STATE

Grant the permissions

Run the following command to grant the monitoring user with the VIEW DATABASE STATE permission. Replace MONITORING_USER_NAME with the name of your monitoring user.

grant VIEW DATABASE STATE to MONITORING_USER_NAME

Deadlock Collection feature

  • Role needed: DB_OWNER (or CONTROL permission)

Enable the feature

  1. Run the following command to add the monitoring user to the DB_OWNER role. Replace MONITORING_USER_NAME with the name of your monitoring user.
    alter role DB_OWNER add member MONITORING_USER_NAME
  2. Restart monitoring of the database instance.
  3. Enable deadlock collection, if not already enabled:
    1. From the DPA home page, click the monitored database instance.
    2. Click the Deadlocks tab.
    3. Click Enable Deadlock Collection > Enable for THIS instance.

    Note: DB_OWNER is the most powerful role for contained users. If you add the monitoring user to this role, the features below will start working as well.

Kill Session feature

  • Permission needed: KILL DATABASE CONNECTION

Enable the feature

Run the following command to add the KILL DATABASE CONNECTION permission to the monitoring user. Replace MONITORING_USER_NAME with the name of your monitoring user.

grant KILL DATABASE CONNECTION to MONITORING_USER_NAME

Show Live Plans feature

  • Permissions needed: SHOWPLAN
  • Roles needed: db_datawriter and db_datereader

Enable the feature

Run the following command to add the SHOWPLAN permission to the monitoring user. Replace MONITORING_USER_NAME with the name of your monitoring user.

grant SHOWPLAN to MONITORING_USER_NAME
alter role db_datawriter add member MONITORING_USER_NAME
alter role db_datareader add member MONITORING_USER_NAME
Last modified
14:06, 13 Jun 2017

Tags

Classifications

Public