Cool Tips‎ > ‎Connectivity‎ > ‎

Connectivity Capture Server Logon Events

Applicability:

                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Tested (SP2 and later)
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown
Date:        5 Oct 2010

Description

Utilises a logon trigger to capture logon information for every connection to the server.
 
The data here is extracted through the mocrosoft events xsd definition, specifically from the EVENT_INSTANCE_LOGON complextype.
The full definition of this xsd can be found on the Microsoft web site.  The definition is available in the Program Files folder of the SQL Server isntallation, but the location differs depending on SQL Server version, so the online version is easier to find.
 
NOTE:  For SQL Server 2005, this functionality was only introduced in SP2

Code

 Create the storage table:

 --Create the dbo.ServerLogonHistory Table
CREATE TABLE dbo.ServerLogonHistory
    
(
                
EventType   VARCHAR(512),
                
PostTime    DATETIME,
                
SPID        INT,
                
ServerName  VARCHAR(512),
                
LoginName   VARCHAR(512),
                
LoginType   VARCHAR(512),
                
SID         VARCHAR(512),
                
ClientHost  VARCHAR(512),
                
IsPooled    BIT
    
)
GO

 
 

Create the trigger:

 --Create the Logon Trigger Trigger_ServerLogon
CREATE TRIGGER Trigger_ServerLogon
            
ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON
AS
BEGIN
DECLARE
@data XML

SET        
@data = EVENTDATA()
    
INSERT INTO dbo.ServerLogonHistory
SELECT @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(512)')
        ,
@data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
        ,
@data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(4)')
        ,
@data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(512)')
        ,
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(512)')
        ,
@data.value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(512)')
        ,
@data.value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(512)')
        ,
@data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(512)')
        ,
@data.value('(/EVENT_INSTANCE/IsPooled)[1]', 'nvarchar(1)')
END

GO

 
ċ
Capture connection information using DDL trigger - including pooling info.sql
(1k)
Andy Hughes,
Jun 7, 2012, 10:54 AM
Comments