How to capture SQL login details with Extended Events

Question : I have to capture logon information details for a specific logon on a SQL Server.   Specifically – the client_hostname, nt_username & username. What i’m looking for is a log recording a successful connection made to the server.     The event should be triggered a) when a connection is made & b)   from a connection pool. 

 

Answer:   SQL Server  Extended Events are your friend . This example  creates a Extended Event for a logon called “mylogon”.  When a connection based on the situations you requested a) new connection or b) connection from a connection pool  

CREATE EVENT SESSION [Track_Logon] ON SERVER 
ADD EVENT sqlserver.login(
    ACTION(sqlserver.client_hostname,sqlserver.nt_username,sqlserver.username)
    WHERE ([sqlserver].[session_server_principal_name]=N'mylogon'))
ADD TARGET package0.event_file(SET filename=N'C:temptrack_logon.xel')
GO
ALTER EVENT SESSION [Track_Logon]  
ON SERVER  
STATE = start;  



Go to Source of this post
Author Of this post: Jack Vamvas
Title Of post: How to capture SQL login details with Extended Events
Author Link: {authorlink}