Exposing SQL Server DMV Data to developer and other non-DBA Users

As a developer, it is very useful to have access to a number of SQL Server's Dynamic Management Views so you can view performance related statistics about how your application is interacting with the database.  This includes finding which of your SQL Statements are taking the longest, if SQL Server thinks there are any missing indexes and usage data for your existing indexes.

Unfortunately, access to DMV's in SQL Server requires the VIEW SERVER STATE permission.  Many DBA's are reluctant to grant this permission to members of the development team because of the amount of data that one can see when they have this level of access.  Unfortunately, this removes an important set of performance tuning tools from the developer's toolbox or forces them to ask a DBA for help whenever they need this information.  There has to be a better way.

What we need is a way that we can grant to a developer or any other non-DBA type user access to a select subset of DMV's information.  For example, it is useful for a developer to see query execution statistics, missing index information and index usage data.  Further, we would like to limit the information to just the database a developer is working in.  So how do we do this?

You would think this would be easy to do--create a view as a dba user that exposes only the information you want a developer to access and then grant the developer SELECT permission on that view.  This fails though with the following message.

Msg 300, Level 14, State 1, Line 1 VIEW SERVER STATE permission was denied on object 'server', database 'master'. Msg 297, Level 16, State 1, Line 1 The user does not have permission to perform this action.

The next thought you might have is to create a table valued function with EXECUTE AS set to a user that has permissions to view the DMV's.  However, this also fails with an error message complaining about the module not being digitally signed.

There is a solution, but it turned out to be much more complex than I would have initially thought.  My approach is based on this blog post by Aaron Betrand, though I had to do things a little bit differently than he did.

The Solution

What really helps is to visualize the solution before we start going through the individual steps, so here is what it looks like.

What we have to do is create a database (called PerfDB in this example) that is going to be a holding area for a set of table valued functions and views that we create to expose our DMV information.  This database will have the option TRUSTWORTHY ON option set, which will allow our table valued function to execute as a user with VIEW SERVER STATE permissions and access the DMV's (this gets around the error about function not being digitally signed that was mentioned above).  Then, we can create the rest of our objects to expose the DMv information we want to.  This approach gives us a way to expose just the information we want, which is what we are after.

Let's walk through the individual steps we need to take for all of this to work.


Step 1 - Create your PerfDB container database
So to get started, we'll create the PerfDB database.  I did this in SQL Server Management Studio using the UI, mainly because that is the easiest way for me to do it.

Once the database is created, I open a query window and run the following command to set the TRUSTWORTHY option to ON.

1	ALTER DATABASE PerfDB SET TRUSTWORTHY ON;

Some DBA's might have some concerns about setting TRUSTWORTHY to ON, but that is the reason for using a separate database that is just a container for the objects we create.  By separating these objects out into a separate database, we've limited any security risks we might have.

Step 2 - Create the Custom DMV Performance Functions in the PerfView Database
Now we are ready to create a table valued functions which will query DMV's of interest for us.  In this example, I am creating a function that will return back the query execution statistics in SQL Server.  You would create one of these functions for each piece of DMV info you wanted to expose.

The function basically just executes a query against a DMV or DMVs and returns that data in a table object.  In this function, you can see I am taking in a parameter of the database id so that in the next step, I'll be able to able to limit access for a user to only seeing data for one database.

You will need to create this function as a user that has the VIEW SERVER STATE privilege, so this could be an existing user or you could create a separate, dedicated user that owns this function and others like it.  What is important is that this function has the WITH EXECUTE AS SELF option set, so that it will run as the user who created the function.  This will allow this function to query the DMV's even if the calling user does not have permission to see the DMV's.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57

CREATE FUNCTION dbo.GetSqlExecutionStatistics(@database_id INT) RETURNS @x TABLE ( DatabaseId INT, DatabaseName VARCHAR(100), ExecutionCount BIGINT, CpuPerExecution BIGINT, TotalCpu BIGINT, IOPerExecution BIGINT, TotalIO BIGINT, AverageElapsedTime BIGINT, AverageTimeBlocked BIGINT, AverageRowsReturned BIGINT, TotalRowsReturned BIGINT, QueryText NVARCHAR(max), ParentQuery NVARCHAR(max), ExecutionPlan XML, CreationTime DATETIME, LastExecutionTime DATETIME ) WITH EXECUTE AS OWNER AS BEGIN INSERT @x (DatabaseId, DatabaseName, ExecutionCount, CpuPerExecution, TotalCpu, IOPerExecution, TotalIO, AverageElapsedTime, AverageTimeBlocked, AverageRowsReturned, TotalRowsReturned, QueryText, ParentQuery, ExecutionPlan, CreationTime, LastExecutionTime) SELECT [DatabaseId] = CONVERT(int, epa.value), [DatabaseName] = DB_NAME(CONVERT(int, epa.value)), [ExecutionCount] = qs.execution_count, [CpuPerExecution] = total_worker_time / qs.execution_count , [TotalCpu] = total_worker_time, [IOPerExecution] = (total_logical_reads + total_logical_writes) / qs.execution_count , [TotalIO] = (total_logical_reads + total_logical_writes) , [AverageElapsedTime] = total_elapsed_time / qs.execution_count, [AverageTimeBlocked] = (total_elapsed_time - total_worker_time) / qs.execution_count, [AverageRowsReturned] = total_rows / qs.execution_count, [TotalRowsReturned] = total_rows, [QueryText] = SUBSTRING(qt.text,qs.statement_start_offset/2 +1, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2 ELSE qs.statement_end_offset end - qs.statement_start_offset) /2), [ParentQuery] = qt.text, [ExecutionPlan] = p.query_plan, [CreationTime] = qs.creation_time, [LastExecutionTime] = qs.last_execution_time FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) p OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa WHERE epa.attribute = 'dbid' AND CONVERT(int, epa.value) = @database_id; RETURN; END

Step 3 - Create a Database Specific View to Wrap Your Function
I'll create a view specific to the database I want to expose data for and pass in the appropriate database id to the function created in step 2.  The reason I am doing this is because ultimately, I'll give my users SELECT access on this view and not directly on the table valued function.  This way I have the logic (my query) in just one place (the function) that I can use over and over again, but what my users will see is their specific view for their specific database.

1 2	CREATE VIEW AppDatabaseName_SqlExecutionStatistics AS SELECT * FROM GetSqlExecutionStatistics( <<application database id>> );

You would replace AppDatabaseName in the name of this view with the name of your application database.  You would also create a view for each different app database you wanted developers to have access to.

If you need to know the database id of your database, this information can be obtained from the sys.databases view in the master database.


Step 4 - Grant Permission for Users to Query From the View
For your user's to be able to query this information, we need to grant them the SELECT privilege to the view we just created in step 4.  But before we do that, we need to map their login to a user in the PerfDB database.  Basically, they have to have access to the database before they can even see the view, so we can do this in the Management Studio GUI or by running the following command.

1 2 3

USE PerfDB; CREATE <<username>> FROM LOGIN <<user login name>>;

For every user you want to have access to this information, you need to do this.  So that means if you have five developers who need access to this information, you'll need to give all five of those developer logins access to the PerfDB database in this way.

Then, we can grant permission so those user's can query the view.

1 2	GRANT SELECT ON AppDatabaseName_SqlExecutionStatistics TO <<username>>;

You will probably want to create a role that has select permissions for all of the DMV views you create for a database, and then put user's inside of that view.  The important point though is that you have to get the user's permission to be able to select from the view.


Step 5 - Querying the Data
Querying the data is as simple as Querying the view we just created, and this can be done by our non-DBA user who does not have VIEW SERVER STATE permission.

1 2	SELECT * FROM PerfDb.dbo.AppDatabaseName_SqlExecutionStatistics;

If you want to make things a little easier, you can create a synonym in the application database so user's don't have to fully qualify the object name in their queries.

1 2 3

CREATE SYNONYM [dbo].[QueryExecutionStatistics] FOR [PerfDb].[dbo].[AppDatabaseName_SqlExecutionStatistics] GO

And now users can simply run this query

1	SELECT * FROM QueryExecutionStatistics;

Now, normal user's who have access to this view can access the information contained within.  This also allows you to selectively choose what information is exposed from your DMV's since you control the query inside of the table valued function and you can limit the results to data from just a single database.

Its unfortunate there isn't a more straightforward way to do this, but at least it can be done.  And of course all of this work can be incorporated into scripts, making it much easier to run.