SQL Server – Dynamic Management Views – Granting Access to Non Sysadmin


One the great satires in my life is seeing how songs lyrics apply.  I like Craig Morgan’s -” That’s What I Love About Sunday” and I will use it as background music as I opined about Microsoft Connect (  http://connect.microsoft.com ).

I know it is a good, well founded idea for Vendors to allow the community to solicit bug fixes and suggest enhancements.


But, the satire about it, is how quickly MSFT engineers quickly stamp the ticket as noted below:

 The following fields or values changed:

Field “Status” changed from “Active” to “Resolved“.

Field “Resolution” changed from “Not Set” to “Won’t Fix“.

About 3 weeks ago, I created a Connect Item:

Implement “View Database State” permission

The underlying premise ( as described in the ticket ) is:

  • In a multi-tenant environments, DBAs can quickly say “NO to developers and Application Engineers requesting” “View Server State” permissions.
  • The basis of the No might be that “View Server State” leaks information (object names, fragmentation level, missing indexes) about other databases.

And, some of the opportunities are:

  • Access to Dynamic Management Views offers unparalleled insight into database performance constraints
  • Faster development
  • Interested parties do not have to wait till problem occurs to be aware of potential problems ( missing indexes, fragmentation, I/O Stats)
  • Need is tangential to others that are already in place at database level such as “Showplan”

Microsoft SQL Server Teams Response

So here is Microsoft’s response:

Do you mean more DMVs should be modified to only require VIEW DATABASE STATE? VIEW DATABASE STATE does exist – see:
select * from sys.fn_builtin_permissions(null) where permission_name = ‘VIEW DATABASE STATE’



I went ahead and ran:

select *
from sys.fn_builtin_permissions(null)
where permission_name = 'view database state'

And, got back


Here is my take

  • I am thankful that now I know about “view database state” role
  • But, what use is it? Which Dynamic Management Views ( DMVs ) does it have access to.  Are there plans to extend it further
  • Why close a ticket and mark it as completed when it appears that the engineer is still soliciting a feedback
  • Preserving Anonymity serves its own purpose, but that anonymity mostly benefits the person invoking it.
  • On the other-hand it hinders the communal spirit and openness that was probably the intent of the forum’s founding members
  • Another point that comes to mind is how does management measure the value of these forums – do you measure it based on simple metrics — lag time between ticket creation and closure, outstanding tickets
  • Or does one have to dig deeper into follow-up feedback.  Once a ticket is closed, can the original initiator give a feedback and satisfaction level


As I said earlier, there is so much I love about Microsoft’s Connect.  And, yet there are a few things that drives delay and opacity.


Server and Database Roles

  • Permissions Hierarchy (Database Engine)
  • Database Engine Fixed Server and Fixed Database Roles
  • Database Engine Permission Basics

One thought on “SQL Server – Dynamic Management Views – Granting Access to Non Sysadmin

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s