The DMV’s; newly introduced in SQL Server 2005 gives database administrator information about the current state of the SQL Server machine. These values will help the administrator to diagnose problems and tune the server for optimal performance. DMV’s are designed to be used instead of system tables and various other functions provided in SQL Server 2000.
Two types of dynamic management views:-
Server-scoped DMV: Stored in Master Database
Database-scoped DMV: Specific to each database
Permission to Execute DMV [Security]
To query a Server scoped DMV the database user must have SELECT privilege on VIEW SERVER STATE and for database scoped DMV the user must have SELECT privilege on VIEW DATABASE STATE.
GRANT VIEW SERVER STATE to <Login >
GRANT VIEW DATABASE STATE to <User >
If you want to deny a user permission to query certain DMV’s you can use the DENY command to restrict access to specific DMV.
