CODEWITHSUNDEEP
sql-serverextended-events
Tagamoga
Tagamoga

Reputation: 360

How Do I find the user for an extended event?

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:

Sql 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!

Upvotes: 1

Views: 1105

Answers (1)

Preben Huybrechts
Preben Huybrechts

Reputation: 6111

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)

extended events - global fields

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>

Upvotes: 1

Related Questions