By: Aaron Bertrand | Comments (4) | Related: > 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:
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:
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
- 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)
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips