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