Give SQL Server users access to Extended Events

Problem

In SQL Server 2008, in order to take full advantage of extended events, the login needs to have CONTROL SERVER permissions. In SQL Server 2012, a new server-level permission was added: ALTER ANY EVENT SESSION, giving you much more granular control. In order to even just view the output of extended events sessions, the VIEW SERVER STATE permission is required – which may provide more insight into the system than you’re willing to hand out. But what if you have users you want to be able to consume extended events data, without all of those inherent privileges? (I actually encountered this problem indirectly, through a colleague, who could not query XEvent data due to a lack of being granted VIEW SERVER STATE permissions.)

Solution

Let’s create a login and a simple database where the user will operate.

CREATE DATABASE WorkingDatabase;
GO
CREATE LOGIN XELogin WITH PASSWORD = N'foo', CHECK_POLICY = OFF;
-- not a recommended password ---------^^^ 
GO 
USE WorkingDatabase;
GO
CREATE USER XEUser FROM LOGIN XELogin;
GO

Now, we haven’t granted any permissions at all to this user. If they try to view data from the system_health extended events session:

SELECT event_data
  FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL);

They will get these errors:

Of course, giving the login VIEW SERVER STATE would fix this, but as mentioned above, we may not want them to have all of those implied rights, and we may not even want them to see everything in a single extended events session. Let’s say we want XEUser to only be able to see deadlock information, for example.

The first thing you might think to do is to create a table-valued function with EXECUTE AS OWNER, so that the caller’s permissions are not used. (EXECUTE AS can’t be used for inline table-valued functions or views, and a stored procedure is often less than ideal because it is not conducive to things like filtering and joining.) So you might create a function like this:

USE WorkingDatabase;
GO
CREATE FUNCTION dbo.Deadlocks()
RETURNS @x TABLE (DeadlockGraph XML, LastStart DATETIME)
WITH EXECUTE AS OWNER
AS
BEGIN
  INSERT @x(DeadlockGraph, LastStart)
  SELECT DeadlockGraph, DeadlockGraph.value(
    N'(deadlock/process-list/process[1]/@lasttranstarted)[1]', N'datetime')
  FROM
  (
    SELECT DeadlockGraph = XEvent.query(N'(event/data/value/deadlock)[1]')
    FROM
    (
      SELECT XEvent = CAST(event_data AS XML) 
        FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
        WHERE [object_name] = N'xml_deadlock_report'
    ) AS x
  ) AS y;
  RETURN;
END
GO
GRANT SELECT ON dbo.Deadlocks TO XEUser;

The function gets created successfully; however, when you run a query against it:

SELECT DeadlockGraph, LastStart FROM dbo.Deadlocks();

You get a new error message:

I was surprised to discover that this error happens even for members of the sysadmin role. It seems there are some extra hoops you need to jump through in order to run this sort of query from a function and grant access.

Here is what worked for me: putting the extended event access into a database with the TRUSTWORTHY setting on. So, create a new, empty database, and add that same user (you would probably use roles for some of this if there were more than one user, now or in the future):

CREATE DATABASE XESource WITH TRUSTWORTHY ON;
GO
USE XESource;
GO
CREATE USER XEUser FROM LOGIN XELogin;
GO

Next, we can just create a view, since with TRUSTRWORTHY on, we no longer need to use EXECUTE AS:

USE XESource;
GO
CREATE VIEW dbo.Deadlocks
AS
  SELECT DeadlockGraph, LastStart = DeadlockGraph.value(
    N'(deadlock/process-list/process[1]/@lasttranstarted)[1]', N'datetime')
  FROM
  (
    SELECT DeadlockGraph = XEvent.query(N'(event/data/value/deadlock)[1]')
    FROM
    (
      SELECT XEvent = CAST(event_data AS XML) 
        FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
        WHERE [object_name] = N'xml_deadlock_report'
    ) AS x
  ) AS y;
GO
GRANT SELECT ON dbo.Deadlocks TO XEUser;

By default, XEUser won’t have any other privileges or really any other escalation potential in this database, but feel free to secure them tighter by generously issuing REVOKE / DENY and ensuring the server-level login doesn’t have any elevated permissions through group or role membership.

Now, go back to the database the user works in, drop the function and create a view in its place:

USE WorkingDatabase;
GO
DROP FUNCTION dbo.Deadlocks;
GO
CREATE VIEW dbo.Deadlocks
AS
  SELECT DeadlockGraph, LastStart FROM XESource.dbo.Deadlocks;
GO
GRANT SELECT ON dbo.Deadlocks TO XEUser;
GO

Now log in as that user, and run a query against the view:

USE WorkingDatabase;
GO
SELECT DeadlockGraph, LastStart FROM dbo.Deadlocks;

Success!

Now, you could skip some of those steps by just setting the working database to TRUSTWORTHY ON, instead of having an extra layer of abstraction. But sometimes an extra layer of abstraction is a good thing. This is not a setting you should bat around lightly, and you should limit the number of things that a TRUSTWORTHY database can do. Before using this solution in either configuration, please read up on TRUSTWORTHY and impersonation, starting here, here, and here.

Next Steps

• On a test system, experiment with these options to see if the models can work for your scenario of giving users limited access to extended events data.
• See these tips and other resources:
  • An Overview of Extended Events in SQL Server 2008
  • Getting Started with Extended Events in SQL Server 2008
  • Using Powershell to Monitor the SQL Server system_health Extended Event Session
  • All Extended Events Tips
  • Guidelines for using the TRUSTWORTHY database setting in SQL Server (Microsoft Knowledge Base)
  • Extending Database Impersonation by Using EXECUTE AS (MSDN)
  • Security risks of setting trustworthy = on in sql server 2012 (Stack Overflow)