DMVs and security risk? RRS feed

  • Question

  • Brief background to my situation.  I'm a developer at an older shop, they're only 3 years on a MS shop so there's plenty of historical mindset (DB2 & COBOL).  The developers have read access to production data as we also support the environment but no ability to update or modify code there except through a formal release process.  Our databases are fairly group-centric, so the web team has all their databases on server X, app developers on server Y, etc.

    I want to have the ability to see my query plans as well as hit the DMVs to see things such as index fragmentation, missing indexes etc.  The why, is quite simply support.  I think we need the ability to see whether our code is performing well once it's "left the nest".  The DBAs have denied other groups the ability but they were denied as it's a "security risk" with no further explanation of what that risk was.  Are they telling us porky pies or is there something I'm not seeing?

    I've googled SQL Server 2005 DMV security risk but I'm not finding anything pertinent.  Would anyone help me understand what concerns you would have granting developers the ability to see the management information?
    Tuesday, March 24, 2009 3:36 PM

All replies

  • Hi Charles,

    According to my understanding, if someone has permissions to view Server And Database dynamic views, server and database states can be monitored. This certainly helps in performance tuning and troubleshooting. And in fact this is exactly why dynamic views were introduced in SQL Server 2005 in order to provide DBAs and Developer's more transparency to what's happening inside server and database engine.

    But the another truth is, by monitoring dynamic views one can see the state of the Database and Servers and there is a potential of priviledged information disclosure. Hence, I would say that if there is a proper requirement and if the person/group comes under the appropriate trust circle then only viewing permissions for dynamic management views should be granted.

    There is one link very similar to your query, please have a look at that too http://social.msdn.microsoft.com/Forums/en-US/sqlservicebroker/thread/403ba953-84c2-4525-a253-5aab6859afae/ .

    Please Indicate ("Mark as Answer") if a Post has Answered the Question. And this posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, March 25, 2009 8:26 AM