Applications Systems

Azure SQL Database permissions for DPA monitoring

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

First published date

10/29/2018 9:14 PM

Last published date

9/6/2019 8:52 PM

Overview

This article provides information about Azure SQL database permissions for DPA monitoring.

Product section

Database Performance Analyzer

Resolution

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