Granting Least Privilege for Synapse DMV Access

DP-203 By islever Editorial Last updated

Short answer

A VIEW DATABASE STATE

Question

You have an Azure Synapse Analytics dedicated SQL pool named SQL1 and a user named User1. You need to ensure that User1 can view requests associated with SQL1 by querying the sys.dm_pdw_exec_requests dynamic management view. The solution must follow the principle of least privilege. Which permission should you grant to User1?
  1. VIEW DATABASE STATE Correct Answer
  2. SHOWPLAN
  3. CONTROL SERVER
  4. VIEW ANY DATABASE

Key Takeaways

The question tests knowledge of specific dynamic management view (DMV) permissions in Synapse, where granting database-scoped state visibility avoids unnecessary server-wide privileges.

To monitor Azure Synapse dedicated SQL pool performance via sys.dm_pdw_exec_requests, the VIEW DATABASE STATE permission is required. Community consensus confirms this adheres to the principle of least privilege compared to server-level roles.

Common mistake: Candidates often select CONTROL SERVER or VIEW ANY DATABASE because they assume monitoring requires broad administrative access, failing to recognize that VIEW DATABASE STATE is sufficient and scoped correctly.

Expert Analysis

Why the Answer Is Correct

Granting the VIEW DATABASE STATE permission allows a user to query dynamic management views such as sys.dm_pdw_exec_requests within a specific database without exposing sensitive metadata outside that scope. This aligns perfectly with the principle of least privilege by limiting access to only what is necessary for monitoring execution requests.

Why the Other Options Are Wrong

CONTROL SERVER grants excessive administrative rights across the entire server instance, violating least privilege. VIEW ANY DATABASE allows viewing information about all databases but not necessarily the internal execution states required for DMVs like sys.dm_pdw_exec_requests. SHOWPLAN relates to query plan display and does not grant access to execution request metrics.

Community Comment Notes

Community members unanimously agree on option A, citing official Microsoft documentation that links sys.dm_pdw_exec_requests access specifically to the VIEW DATABASE STATE permission. Comments emphasize that this is a standard security configuration for monitoring dedicated SQL pools.

Official Reference

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-manage-monitor

This link opens the official vendor documentation in a new tab.

Exam Strategy

Always map specific system views or DMVs to their exact required permissions rather than assuming broader roles are needed. When 'least privilege' is mentioned, look for database-scoped permissions over server-scoped ones.

Community Votes

A
100%

100% of anonymous learners picked answer A. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 2 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 2

  1. renan_ineu 👍 1
    A - Correct https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-manage-monitor
  2. tadenet 👍 1 Answered A
    correct

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

How to Verify Result Set Cache Usage in Azure Synapse SQL Pools?

S-grade

Which Diagnostic Setting Tracks Query Start and End Times in Synapse SQL Pool?

S-grade

Which Integration Runtime Connects On-Premises Linux Databases to Azure Data Factory?

S-grade

Which Log Analytics table stores ADF pipeline runs in resource-specific mode?

S-grade

← Back to DP-203 Study Guide