sql-serverextended-events

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!


Solution

  • 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>