What rights are required in SQL Server to create and view an Extended Events session?
In SQL Server 2008 and 2008R2, a user had to be granted CONTROL SERVER to create and run sessions. CONTROL SERVER is roughly equivalent to sysadmin. The difference is that if you grant someone CONTROL SERVER and you don’t want them to access something, you can issue an explicit DENY. Having to do that for a large number of objects would be a pain, though.
When a user is granted CONTROL SERVER, she can access any database’s objects – tables, views, stored procedures, and more.
Here, Developer1 has been granted “Control server”.
Developer1 is able to log in and not only view database information, but add to it.
Most DBAs would, rightfully, be concerned with this level of permission.
In SQL Server 2012, a new permission was added: ALTER ANY EVENT SESSION. This is a much more targeted permission. If I grant a user this right, and he logs into SSMS, he can’t view the data or procedures in a database, logins, or other information, but he can create XE sessions.
Here, Developer2 has been granted “Alter any event session” rights.
He is unable to access data he has not been granted rights to.
Creating an event session is possible, but only with T-SQL. Trying to create a session by right-clicking “Sessions” and selecting “New Session Wizard” or “New Session …” results in the error, “The user does not have permission to perform this action. (Microsoft SQL Server, Error: 297)”.
To use the GUI, the user must be granted “View Server State”. This level of permission will give the user the ability to see many more things in the server, though (but not databases and data in them). One solution to this problem is presented by Aaron Bertrand in his article “Give SQL Server users access to Extended Events”.
You don’t need to give a user sysadmin rights to allow him or her to create XE sessions. Remember the principle of least permissions!