Capture Server Logon Events
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
Date: 5 Oct 2010
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
Create the storage table:
--Create the dbo.ServerLogonHistory Table
Create the trigger:
--Create the Logon Trigger Trigger_ServerLogon