nerdexam
Microsoft

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.

Design and implement data security

Question

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 monitor DB1. The solution must minimize the number of permissions assigned to User1. You create a login for User1. Which permission should you grant to User1?

Options

  • AALTER TRACE
  • BVIEW SERVER STATE
  • CVIEW DATABASE STATE
  • DVIEW ANY DATABASE

How the community answered

(71 responses)
  • A
    14% (10)
  • B
    77% (55)
  • C
    6% (4)
  • D
    3% (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.

AALTER TRACE

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.

BVIEW SERVER STATECorrect

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.

CVIEW DATABASE STATE

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.

DVIEW ANY DATABASE

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

#SQL Server security#DMV permissions#database monitoring#least privilege

Community Discussion

No community discussion yet for this question.

Full 70-473 Practice