For a "simple" Request On a MSSQL Server I get a Column filled with Xmls:
SELECT CONVERT(XML, event_data) As event_data_xml
FROM sys.fn_xe_file_target_read_file('Test*.xel', NULL, NULL, NULL)
The result:
one of these in details:
<event name="login" package="sqlserver" timestamp="2020-07-17T10:09:20.922Z">
<data name="is_cached">
<value>true</value>
</data>
<data name="is_recovered">
<value>false</value>
</data>
<data name="is_dac">
<value>false</value>
</data>
<data name="database_id">
<value>1</value>
</data>
<data name="packet_size">
<value>4096</value>
</data>
<data name="options">
<value>2000002838f4010000000000</value>
</data>
<data name="options_text">
<value />
</data>
<data name="database_name">
<value />
</data>
<action name="attach_activity_id" package="package0">
<value>093CAB81-B0B7-46CF-A807-F32D0469235A-2</value>
</action>
</event>
In these details I do not get any hint, which user invoked this event. So:
How Do If find out by SQL Code which user invoked this event?
Thanks for help!
When setting up your extended event you should expand the event (by clicking on it) and in the Global fields tab check nt_username
and/or username
and any of the other fields that might be interesting. (I.e.: client_hostname, client_app_name)
you should do this for all selected events in your case based on your screenshot, login and logout.
In T-SQL:
-- Drop the existing Login event
ALTER EVENT SESSION [Test_Event] ON SERVER
DROP EVENT sqlserver.login
-- Drop the existing logout event
ALTER EVENT SESSION [Test_Event] ON SERVER
DROP EVENT sqlserver.logout
-- Add a new login event with properties : nt_username and username
ALTER EVENT SESSION [Test_Event] ON SERVER
ADD EVENT sqlserver.login(
ACTION(sqlserver.nt_username,sqlserver.username))
-- Add a new logout event with properties : nt_username and username
ALTER EVENT SESSION [Test_Event] ON SERVER
ADD EVENT sqlserver.logout(
ACTION(sqlserver.nt_username,sqlserver.username))
New events will then contain the selected fields:
<event name="login" package="sqlserver" timestamp="2020-07-27T05:59:26.931Z">
<data name="is_cached">
<value>true</value>
</data>
<data name="is_recovered">
<value>false</value>
</data>
<data name="is_dac">
<value>false</value>
</data>
<data name="database_id">
<value>1</value>
</data>
<data name="packet_size">
<value>8000</value>
</data>
<data name="options">
<value>2000002838f4010000000000</value>
</data>
<data name="options_text">
<value />
</data>
<data name="database_name">
<value />
</data>
<!--Newly selected global fields-->
<action name="username" package="sqlserver">
<value>Domain\Username</value>
</action>
<action name="nt_username" package="sqlserver">
<value>Domain\Username</value>
</action>
</event>