increased but limited access to certain dynamic management views RRS feed

  • Question

  • I am a developer for an application using a database on a SQL Server (ver 9.0.2050) that is also shared by other databases which are unrelated.

    We have had some issues with locking and while ever since they started we have very much been taking locks into account as we code we have never been able to find the root of the problem. we have been denied VIEW SERVER STATE permissions as they would give us access to the state of the other databases as well as ours.

    My solution was a set of functions that return a filtered set of data from certain dynamic management views (specificaly dm_tran_locks, dm_exec_requests, and dm_exec_sessions), using the WITH EXECUTE AS clause to impersonate a user with VIEW SERVER STATE. the problem is that the WITH EXECUTE AS clause will only impersonate a database user and not a server login, and server wide permissions can't be granted to a database user. This is true even if the user is associated with a login that has the server permission.

    If, instead, I use the seporate EXECUTE AS statement then it fails unless the caller (me) has impersonate permissions on the login being impersonated (whereas the WITH EXECUTE AS claus only requires the person creating it to have impersonate on the user being impersonated)

    So, I tried doing both, using the WITH EXECUTE AS clause to impersonate a user associated with a high-permisison login and then the EXECUTE AS statement to further impersonate a login with the permissions I need. I found that a database user can't impersonate a server login even if they are associated with one-another.

    All that to ask, how can I gain access to the dynamic management views listed above (or sunstantialy the same information) but only for the databases that I am allowed to access it for?

    Note that I have the full cooperation of the DBA for the server, we are just trying to figure out how to do it. I also have a copy of SQL Server Express (ver 9.0.3068)  for testing, on which I have full access.


    Tuesday, December 30, 2008 5:00 PM



      As you already mentioned, using EXECUTE AS in a module is a good solution for a DB-restricted solution, but in the case of server-scope permissions, my recommendation would be using digital signatures.  By creating a login mapped to the certificate and granting the permissions to this login, you should be able to accomplish the task you need.

        Laurentiu wrote a demo that should be very useful as a guide for signing a SP and giving server-scoped permissions via the signature: http://blogs.msdn.com/lcris/archive/2005/06/15/sql-server-2005-procedure-signing-demo.aspx

      Please let us know if this information helped or if you have any further questions.

       -Raul Garcia
        SQL Server Engine

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, December 30, 2008 8:19 PM