Trigger firing when users log on to the server

logon_event_trigger.sql

/**********************************************************************************************************************
 * Name:           trg_LogonAudit
 * Type:           Server-level DDL Trigger
 * Description:    Captures successful logon events for all users on the SQL Server instance and records them in
 * a dedicated audit table.
 *
 * Author:         Marcus Holmgren
 * Created Date:   2025-07-07
 *
 * Dependencies:   Requires a table to store the audit data.
 * Example table structure:
 *
 * CREATE TABLE dbo.ServerLogonLog (
 * LogID INT IDENTITY(1,1) PRIMARY KEY,
 * LoginName NVARCHAR(128) NOT NULL,
 * LoginDate DATETIME2(3) NOT NULL,
 * SessionID SMALLINT NOT NULL,
 * SourceIPAddress VARCHAR(48)
 * );
 *
 * Notes:          The trigger is created with `WITH EXECUTE AS 'sa'` to ensure it has sufficient permissions
 * to read session information for any user logging in. This requires careful security
 * * consideration in a production environment.
 **********************************************************************************************************************/

CREATE TRIGGER trg_LogonAudit
ON ALL SERVER
-- The `WITH EXECUTE AS 'sa'` clause ensures the trigger has permissions to query system views for any user.
-- This context is dropped after the trigger completes. It's a common pattern for logon triggers.
WITH EXECUTE AS 'sa'
-- The `AFTER LOGON` clause specifies that the trigger will fire after a logon attempt has been authenticated.
AFTER LOGON
AS
BEGIN
    -- SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages for each statement in a stored procedure.
    -- This is a best practice for triggers and stored procedures to reduce network traffic.
    SET NOCOUNT ON;

    -- The `EVENTDATA()` function can be used to capture information about the event that fired the trigger.
    -- While not used here, it's useful for more advanced logon triggers (e.g., checking for specific application names).

    -- Insert the logon details into the audit table.
    -- We retrieve the client's network address directly from `sys.dm_exec_connections`.
    INSERT INTO YourDatabaseName.dbo.ServerLogonLog (
        LoginName,
        LoginDate,
        SessionID,
        SourceIPAddress
    )
    SELECT
        ORIGINAL_LOGIN(),   -- The original login name of the user connecting.
        GETDATE(),          -- The current date and time of the logon event.
        c.session_id,       -- The unique session ID (SPID) for this connection.
        c.client_net_address -- The IP address of the client machine.
    FROM
        sys.dm_exec_connections AS c
    WHERE
        c.session_id = @@SPID; -- @@SPID is a global variable that holds the session ID of the current user process.

END;
GO -- This command signals the end of a batch of Transact-SQL statements to the SQL Server utilities.