3

I am trying to create a trace in Extended Events filtering with logins, in profiler we can filter with login names but I do not see that option in XE. How do I do that?

3

In SQL 2016 there are 5 filter predicates you can use to filter logins.
For NT login/user you can use all 5 but SQL login/user you can use number 3,4 and 5 only.

  1. sqlserver.nt_username-Collect NT username
  2. sqlserver.session_nt_username-Get the current session NT user
  3. sqlserver.server_principal_name-Get the name of the Server Principal in whose context the event is being fired.
  4. sqlserver.username-Get the current username
  5. sqlserver.session_server_principal_name-Get the name of the Server Principal that originated the session in which the event is being fired.

Using tsql, you need to change my code depending on what kind of account and real value you are using.

    ALTER EVENT SESSION [test] ON SERVER 
    ADD EVENT sqlserver.sp_statement_completed(
     WHERE ((((([sqlserver].[username]=N'') 
       AND ([sqlserver].[session_server_principal_name]=N'')) 
       AND ([sqlserver].[server_principal_name]=N'')) 
       AND ([sqlserver].[session_nt_user]=N'')) 
       AND ([sqlserver].[session_server_principal_name]=N'')))
    GO

From GUI:

Once you pick select events, highlight event and click configure.

enter image description here

Under filter set your And/or condition, filed, operator, value. enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.