In this article, I want to outline how to use fine-grained permissions to lock down Extended Event Tracing for different roles and personas.

Background

When it comes to performance monitoring and troubleshooting, 3 major first-party tools are utilized regularly, either directly, or via other tools that build upon them:

• Windows Performance Monitor
• SQL Server dynamic management views (aka DMVs) (in conjunction with catalog views)
• Extended Events (Extended Events overview ).
• Query store could be considered a fourth, but as it uses catalog views and is covered by the same permission as the DMVs (VIEW DATABASE PERFORMANCE STATE) one can argue they belong to the DMV approach.

I have already blogged about “Using Query Store with least privileges instead of db_owner to achieve Separation of Duties”. The only thing to add would be that instead of VIEW DATABASE STATE, one can now use VIEW DATABASE PERFORMANCE STATE to query the query store views.

With SQL Server 2022, the permissions for using Extended Events  (aka XEvents)have become granular as I had announced here: New granular permissions for SQL Server 2022 and Azure SQL to improve adherence with PoLP

Here I will show practical examples of how to utilize XEvents under the Principle of Least Privilege. The goal of the course is to grant only the minimally required sets of permissions for distinguished tasks or roles.

Altogether we now have 18 permissions for XEvents, if we look at SQL Server and Azure SQL Database together. That is because in SQL Server XEvents exist on the server level, and in Azure SQL database on database level.

The new permissions are hierarchically placed under ALTER but not further covering one another to allow a proper principle of privilege approach:

If you compare the 9 permissions on server level with the database level permission, you will notice the only difference is the addition of the word “DATABASE” as part of the permission name:

Following are some examples of how these permissions can be split up for different roles.
I will use the server level permissions as example, but this concept can easily be translated to database level in a shared hosted Azure SQL Database environment for example.

Role 1) XEvent session administrators – manage all Extended Event sessions with full privileges

There will always be someone with higher privileges, who will

• Implement and XEvent sessions and let them run continuously
• Prepare XEvent sessions ready to be started by others only when the need comes up
• Prepare basic sessions as a starting point for others who may be allowed to add more events or targets
• And of course, they will have the power to stop and remove any sessions

This role will have the ALTER ANY EVENT SESSION permission. (This permission is not new; it has existed since XEvents were introduced in SQL Server 2008 and was the only available permission until SQL Server 2022)

Role 2) XEvent support – start up provided sessions as needed

Another group of users can be those who have less destructive permissions and have merely the power to start any XEvent sessions that the XEvent session admins have prepared and view the collected data.

The permission required is: ALTER ANY EVENT SESSION ENABLE

In addition, to see the list of sessions (in T-SQL as well as in SSMS), they should also have the permission VIEW SERVER PERFORMANCE STATE.

To ensure that members cannot accidentally stop other sessions that may be important to keep running, this role will not have permission to stop sessions ALTER ANY EVENT SESSION DISABLE.
This is how these permissions are granted – to a server role instead of the logins directly as a best practice:

As a result, members of this role can list all defined sessions on the server, start any of them but not stop any, and they can also see the collected data.

If viewing the collected data should not be included, then VIEW SERVER PERFORMANCE STATE would have to be omitted. But then these users would not be able to browse the system for which sessions have been defined. They would have to rely on using T-SQL and be given the names of sessions.

Using the XEvent Profiler sessions

Some of you may be using the built-in “XEvent Profiler” node, which is nothing else but a simple way to create 2 predefined session. Curiously, just to make things a bit more complicated, the sessions that will be created when you click on the names are different from what the name under the “XEvent Profiler” node says.

“Standard” will create a session named “QuickSessionStandard”

“TSQL” will create a session named “QuickSessionTSQL”

Now as I said, clicking on these nodes will create sessions, they will not merely be started. And our XESupport will not have the CREATE-permission.

If they try to use this functionality, this will fail with a permission error.

But the solution is quite simple: someone with the necessary permissions, like our Admins, has to launch these “quick sessions” once and then stop them again. They will show up together with the regular sessions under the Management – Extended Events node, from where the support role can start them if needed – or using T-SQL, using the proper name.

“Behind the scenes” on permission naming

In case you are wondering about the permission names (ENABLE/DISABLE) vs the command names (START/STOP): When my team was deciding the proper permission names, we already foresaw that we should use the same permission name for other features that have a start/stop logic.

Other features in T-SQL that have the notion of being started/stopped are Endpoints, Service Broker Queues, and Audits. For the sake of inconsistency :-D, ALTER QUEUE uses STATUS = ON | OFF and ALTER AUDIT uses the WITH (STATE = ON | OFF) syntax.

(Auditing happens to be based on the internal architecture of Extended Events but for no particular reason, the syntax differs from Extended Events.)

For the permission name, we wanted to ensure a common name that would make sense for all features, including future functionalities. That’s why we decided to go with enable/disable as terms, which then also were used for the Purview RBAC action verbs that were developed in parallel.

This is the naming style in Purview RBAC: Microsoft.Sql/Sqlservers/ExtendedEventSessions/State/Enable
(more examples here: Use Microsoft Purview to provide at-scale access to performance data in Azure SQL and SQL Server )

By the way: ALTER ENDPOINT uses STATE = STARTED | STOPPED | DISABLED. So, it will be interesting to see how that will be solved once in future extensions by RBAC or TSQL permissions even. Yes, I realized this too late. 🙁

Role 3) XEvent Admin helper – extend basic extended event sessions

Another role may be a group of people that is empowered to help adjust existing extended event sessions by adding more events and targets, but not removing existing parts of the sessions.

This is possible because the ADD EVENT and ADD TARGET sub-commands of the ALTER EVENT SESSION-command are controlled by separate permissions from the DROP sub-commands.

Adding and modifying predicates for newly added events falls under ALTER ANY EVENT SESSION ADD EVENT permission just as one would expect by the syntax. The same applies to adding Actions.

It is important to note that this also means that it is not possible to grant permission to change predicates for existing events. This is because behind the scenes, changing predicates and actions requires dropping and re-adding the same event with the new predicate/action

Like so:

Therefore, the permission required for that would be ALTER ANY EVENT SESSION DROP EVENT.

Role 4) XEvent data reader – can view collected data

To view the collected data from XEvent sessions, there are 2 options besides the UI depending on the target type used (memory or file) (Targets for Extended Events):

• query DMVs using XQuery for the targets that work in memory
• Query the system function fn_xe_file_target_read_file for the file target

All of those are covered by the VIEW SERVER PERFORMANCE STATE-permission respectively the VIEW DATABASE PERFORMANCE STATE-permission.

Solely reading XEvents data but no other DMVs is not possible. It is unlikely that this would increase security by much, given that XEvents can expose similar information as DMVs.
Also, if we would have started going down that road, we would have had to create permissions for every sub-component or topic covered by the over 1000 DMVs and Catalog views that we have in SQL Server. An almost impossible task given that DMVs were never designed to strongly separate, and even if, the usefulness of this mega-task would have been super low after all since analyzing SQL Server usually spans multiple areas.

If you have the requirement to allow a role to read data from XEVent targets and no other DMVs solely, then there is still the solution to use custom code:
This solution essentially means that instead of directly granting direct access to the DMVs, one would create stored procedures that internally select the data from the XEvent targets.
The user would be bound to the stored procedure’s logic and may expose via parameters. The permission to grant would be EXECUTE on the stored procedure (or better on the schema level if properly designed).

This approach is depicted in my article about delegation here: Security concept: Delegation of Authority.
More examples and explanations of the different options regarding authorization can be found in this article by Erland Sommarskog here: Packaging Permissions in Stored Procedures: http://www.sommarskog.se/grantperm.html

Conclusion

9 distinct permissions instead of just one uber-permission allows for better role-separation and adhering to the Principle of Least Privilege when needed. I would not expect everyone to require this level of separation, but if you need it, this outlines some of the possibilities.

Limitation and feature-request to Microsoft

As I have pointed out before, there is one big gap in the Extended Events architecture: the concept of an owner:

While there are a lot of opinions about the value of a MAC vs a DAC system, SQL Server is based on DAC and uses ownership to control access. Extended Events are one of the few objects that do not have an owner though. And the downside is clear when you look at these examples: all of these roles work on an all-or-nothing target. Either you have permission to add events to or drop targets from all event sessions, or none at all. There is no way to limit the space to something like “only the XEvent sessions that were created by you”. There is also no schema, where one could limit the permissions to a group of sessions within the same schema.
The most flexible solution would of course be a label-based permission system, where one could say: roleX can edit all session labeled “VendorApplicationZ” and similar.
This is why I would prefer SQL Server to support a true MAC-based authorization system.

Dreams, right..? 🙂

Anyway, I hope this was informative.

Happy securing

Andreas