70-473 · Question #106
You have a server named SQL1 that has Microsoft SQL Server 2016 installed. SQL1 contains a database named DB1. You need to ensure that a user named User1 can query dynamic management views to…
The correct answer is B. VIEW SERVER STATE. To grant a user minimal permissions to query dynamic management views (DMVs) for monitoring a specific database on SQL Server, you should assign the VIEW SERVER STATE permission.
Question
Options
- AALTER TRACE
- BVIEW SERVER STATE
- CVIEW DATABASE STATE
- DVIEW ANY DATABASE
How the community answered
(71 responses)- A14% (10)
- B77% (55)
- C6% (4)
- D3% (2)
Why each option
To grant a user minimal permissions to query dynamic management views (DMVs) for monitoring a specific database on SQL Server, you should assign the VIEW SERVER STATE permission.
ALTER TRACE permission allows a user to create, modify, or stop traces, which is related to capturing events but not directly for querying existing dynamic management views for state monitoring.
The VIEW SERVER STATE permission allows a user to query all Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs) at the server level, and also grants the ability to query database-scoped DMVs and DMFs across all databases on that server. This permission is commonly assigned to monitoring roles as it provides comprehensive visibility into the server's and all databases' health and performance, thus enabling User1 to monitor DB1.
VIEW DATABASE STATE permission allows a user to query DMVs and DMFs that are specific to a single database. While it would allow monitoring DB1, VIEW SERVER STATE (option B) is a broader permission often used for comprehensive monitoring that includes database-level DMVs across all databases on the server instance.
VIEW ANY DATABASE permission allows a user to see the metadata of any database on the SQL Server instance. It does not grant the ability to query dynamic management views for performance or state monitoring.
Concept tested: SQL Server permissions for monitoring DMVs
Source: https://learn.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver16
Topics
Community Discussion
No community discussion yet for this question.