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?
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.
- sqlserver.nt_username-Collect NT username
- sqlserver.session_nt_username-Get the current session NT user
- sqlserver.server_principal_name-Get the name of the Server Principal in whose context the event is being fired.
- sqlserver.username-Get the current username
- 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
Once you pick select events, highlight event and click configure.