Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Give SQL Server users access to Extended Events


By:   |   Last Updated: 2015-02-26   |   Comments (4)   |   Related Tips: More > 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:

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

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:

Msg 15562, Level 16, State 1
The module being executed is not trusted. Either the owner of the database of the module needs to be granted authenticate permission, or the module needs to be digitally signed.

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


Last Updated: 2015-02-26


next webcast button


next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, February 04, 2016 - 1:15:29 PM - David Back To Top

 

Thanks for the pointers to the TRUSTWORTHY option.  That seems like a potentially useful workaround.

 

Personally I think that a better solution would be to make use of VIEW DATABASE STATE permissions.  SQL Server should allow that as a way to get a filtered subset of events from the session, based on the user's databases.  Alternatively before reading from it, the event session definition could be reviewed to make sure all requested events have a mandatory predicate that filters on a particular database (for which a user has VIEW DATABASE STATE).

 

I suspect we are still in the early development phase for extended events in SQL.  I suspect these rough edges would be smoothed out if more people were to discover the benefits of using xevents as a matter of course.

 

Anyhoo, I created a Connect item. Please vote!

https://connect.microsoft.com/SQLServer/feedback/details/2322709

 

 


Friday, February 27, 2015 - 11:40:26 AM - Henry B Stinson Back To Top
Thanks for the clarification.  I was wondering if you perhaps had meant something like that.
Those things being said, my comment may prevent someone with less experience from making the kind of mistake I was warning against.
 
You might contact Mindy Curnutt (via Linked In if it lets you) and ask her when and where she might be giving the same lecture near you.  Her extended events scripts are gold.
 
 

Friday, February 27, 2015 - 8:03:35 AM - Aaron Bertrand Back To Top

Henry, I did not advocate giving users the ability to *create* Extended Events sessions, nor did I even suggest that this should be done, never mind show how to do it. The code in this tip shows how to give a user *read* access to a specific XE session, in this case one that already exists and runs by default, and to that end even suggests how to only provide access to a subset of that data.

I think perhaps you've read something into this that simply isn't there.


Thursday, February 26, 2015 - 5:08:45 PM - Henry B Stinson Back To Top

This is NOT a good idea.  There are certain extended events traces a person can set up that bring the server to its knees.  This functionality needs to be kept in the domain of the DBAs or at least senior database developers.

Now, that being said, it may be perfectly valid to put this functionality in the hands of database developers not only on dev servers but on test and sometimes (in some companies that do not have enough DBAs or are too small to have a formal structure) in production servers, but they need to be given training.  I just attended a seminar on extended events at a meeting of the NTTSUG (North Texas SS User Group) in Microsoft offices in the DFW area, where the lecturer, Mindy Curnutt, showed some scripts that greatly simplified setting up and querying the most important extended events.  These obviated the intimidating task of learning the now 857 extended events (in 2014).  She also explained how, even with these SQL scripts, a server could be instantly smothered in tracing events if the person setting up parameters for the scripts did not understand certain things.


Learn more about SQL Server tools