There are occasions when database users will reach out to me get elevated database permissions to run DMVs. Members of sysadmin roles can view the results of Dynamic Management Objects but sometimes it There are occasions when database users will reach out to me get elevated database permissions to run DMVs. Members of sysadmin roles can view the results of Dynamic Management Objects but sometimes it is helpful to grant this permission to non-dba personnel if they need to do any performance troubleshooting.
Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose sql server problems, and tune databae performance.
There are two different types of DMVs and functions:
Server-scoped DMVs and functions – These require VIEW SERVER STATE permission on the SQL Server level.
Database-scoped DMVs and functions – These require VIEW DATABASE STATE permission on each of the database.
VIEW SERVER STATE is a server level permission that grants non-sysadmin users the ability to view results of Dynamic Management Views.
Beginning with SQL Server 2005 dynamic management objects are database views or functions that shows specific information or the state of the SQL Server instance for the overall SQL Server or for a Beginning with SQL Server 2005 dynamic management objects are database views or functions that shows specific information or the state of the SQL Server instance for the overall SQL Server or for a given database instance at a given time. Since DMVs were introduces in SQL Server 2005 and with each new release of SQL Server, Microsoft has been adding additional DMVs (Dynamic Management Views) to help troubleshoot the performance of the SQL Servers. These objects are helpful to monitor the database server in an efficient and controlled manner.
DMVs come in two subsets – Dynamic Management Views (DMVs) and DMFs (Dynamic Management Functions) and are classified as Dynamic Management Objects (DMOs).
DMVs are like any other views where you can select data from them. DMVs require values to be passed to the function just like any other functions. I will go into the details of DMFs in a separate blog.
If the user does not have sysadmin priveleges or has been granted VIEW SERVER STATE permission and tries to run the following as an example:
SELECT * FROM sys.dm_os_wait_statsThe user will see an error like this one:
Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.
We run the following query to grant the user the access to the entire SQL Server Instance:
GRANT VIEW SERVER STATE TO dbuser1This needs to be run on the master database.
Once the above script is executed successfully then that specific user will be able to view Dynamic Management Objects to do any type of performance troubleshooting.
In the case of a individual database scoped access, a user might have data reader access to the database but when the users runs this query:
SELECT * FROM sys.dm_db_partition_stats
They get an error, because this query is trying to access a DMV in the specific database.
WE need to grant VIEW DATABASE STATE for this.
We run the following query to grant the user the access to a specific database:
grant VIEW DATABASE STATE to []
USE [DatabaseName]
grant VIEW DATABASE STATE to [<DBUSer>]Once you run the above script then the user will be able to get a result from:
SELECT * FROM sys.dm_db_partition_statsNow lets talk about the security risks for granting VIEW SERVER STATE permission.
Yes, there is a risk in providing VIEW SERVER STATE to non-dba and non-sysadmin personnel since they aren’t supposed to see this level of intricate database information on the sql instance.
As an example: sys.dm_exec_connections, sys.dm_exec_cached_plans, sys.dm_exec_requests, sys.dm_exec_query_stats, all provide information about execution contexts and plans, and when the user uses these with sys.dm_exec_sql_text() or sys.dm_exec_query_plan(), it will give this user information about the sql code and objects in the databases. This is usually a security violation in some organizations.
The VIEW SERVER STATE permission gives the database user unrestricted access to this information when using these DMVs. Althought they cannot change anything in the database, but they can get detailed database execution info that may not be allowed to view based on the user’s organizational responsibilites. I would analyze this type of access request with a grain of salt and ask some questions as to the security implications of this user requesting access having access to the database execution info. Also the user will be able to use DMV’s to look at queries. If the queries or some query parameters can contain confidential information that the user wouldn’t otherwise be able to see prior to granting this access – allowing VIEW SERVER STATE would allow them to do so (dateofbirth or socialscecurity #s)
Hope this helps clarify the differences between VIEW SERVER STATE and VIEW DATABASE STATE permission grants.