SQL Server Logon Trigger Example


MENU
By:
Overview

In previous chapters of this tutorial we covered DDL triggers. In this chapter I will show you a special type of DDL trigger that fires in response to logon events.

A Word on Logon Triggers

It is intuitive to think that logon triggers fire in response to logon events, but what isn't obvious is that logon triggers only fire on successful logon attempts. In other words, don't think about using logon triggers as an audit method for failed logon attempts. Usually logon triggers are used for establishing policies of usage, like limiting the number of sessions for certain logins or to audit successful logins.

Inside a logon trigger you can use the Transact SQL security functions (https://docs.microsoft.com/en-us/sql/t-sql/functions/security-functions-transact-sql) as well as the set of dynamic management views to obtain information about the login. But if you do, remember that in order to query DMVs you may need to grant the VIEW SERVER STATE privilege to the users, this implies some security risks. Additionally you can query the EVENTDATA() function that will return an XML string with the EVENT_INSTANCE_AUDIT_LOGIN complex type whose definition is available at the events.xsd schema definition file.

Now let's create a logon trigger that audits successful logons. Our first step is to create a table on which we will keep the audit data. In this case I will use a table that matches the EVENT_INSTANCE_AUDIT_LOGIN complex type.

USE SampleDB;
GO
 
CREATE TABLE Event_Instance_Audit_Login
(
    [RowID] INT IDENTITY(1, 1) PRIMARY KEY,
    [EventType] VARCHAR(128) NULL,
    [PostTime] VARCHAR(128) NULL,
    [SPID] INT NULL,
    [TextData] VARCHAR(MAX) NULL,
    [BinaryData] VARBINARY(MAX) NULL,
    [DatabaseID] INT NULL,
    [NTUserName] VARCHAR(256) NULL,
    [NTDomainName] VARCHAR(256) NULL,
    [HostName] VARCHAR(256) NULL,
    [ClientProcessID] INT NULL,
    [ApplicationName] VARCHAR(256) NULL,
    [LoginName] VARCHAR(256) NULL,
    [StartTime] VARCHAR(128) NULL,
    [EventSubClass] INT NULL,
    [Success] INT NULL,
    [IntegerData] INT NULL,
    [ServerName] VARCHAR(256) NULL,
    [DatabaseName] VARCHAR(256) NULL,
    [LoginSid] VARBINARY(85) NULL,
    [RequestID] INT NULL,
    [EventSequence] INT NULL,
    [Type] INT NULL,
    [IsSystem] INT NULL,
    [SessionLoginName] VARCHAR(256) NULL,
    [GroupID] INT NULL
);

In the code section below is the code for the logon trigger that will store the successful logon attempts in the Event_Instance_Audit_Login table.

CREATE OR ALTER TRIGGER [TR_Login]
ON ALL SERVER
FOR LOGON
AS
DECLARE @EventData XML;
SET @EventData = EVENTDATA();
 
INSERT INTO SampleDB.dbo.Event_Instance_Audit_Login
(
    EventType,
    PostTime,
    SPID,
    TextData,
    BinaryData,
    DatabaseID,
    NTUserName,
    NTDomainName,
    HostName,
    ClientProcessID,
    ApplicationName,
    LoginName,
    StartTime,
    EventSubClass,
    Success,
    IntegerData,
    ServerName,
    DatabaseName,
    LoginSid,
    RequestID,
    EventSequence,
    TYPE,
    IsSystem,
    SessionLoginName,
    GroupID
)
VALUES
(@EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(128)'),
 @EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'VARCHAR(128)'),
 @EventData.value('(/EVENT_INSTANCE/SPID)[1]', 'INT'),
 @EventData.value('(/EVENT_INSTANCE/TextData)[1]', 'VARCHAR(MAX)'),
 @EventData.value('(/EVENT_INSTANCE/BinaryData)[1]', 'VARBINARY(MAX)'),
 @EventData.value('(/EVENT_INSTANCE/DatabaseID)[1]', 'INT'),
 @EventData.value('(/EVENT_INSTANCE/NTUserName)[1]', 'VARCHAR(256)'),
 @EventData.value('(/EVENT_INSTANCE/NTDomainName)[1]', 'VARCHAR(256)'),
 @EventData.value('(/EVENT_INSTANCE/HostName)[1]', 'VARCHAR(256)'),
 @EventData.value('(/EVENT_INSTANCE/ClientProcessID)[1]', 'INT'),
 @EventData.value('(/EVENT_INSTANCE/ApplicationName)[1]', 'VARCHAR(256)'),
 @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'VARCHAR(256)'),
 @EventData.value('(/EVENT_INSTANCE/StartTime)[1]', 'VARCHAR(128)'),
 @EventData.value('(/EVENT_INSTANCE/EventSubClass)[1]', 'INT'),
 @EventData.value('(/EVENT_INSTANCE/Success)[1]', 'INT'), @EventData.value('(/EVENT_INSTANCE/IntegerData)[1]', 'INT'),
 @EventData.value('(/EVENT_INSTANCE/ServerName)[1]', 'VARCHAR(256)'),
 @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(256)'),
 @EventData.value('(/EVENT_INSTANCE/LoginSid)[1]', 'VARBINARY(85)'),
 @EventData.value('(/EVENT_INSTANCE/RequestID)[1]', 'INT'),
 @EventData.value('(/EVENT_INSTANCE/EventSequence)[1]', 'INT'), @EventData.value('(/EVENT_INSTANCE/Type)[1]', 'INT'),
 @EventData.value('(/EVENT_INSTANCE/IsSystem)[1]', 'INT'),
 @EventData.value('(/EVENT_INSTANCE/SessionLoginName)[1]', 'VARCHAR(256)'),
 @EventData.value('(/EVENT_INSTANCE/GroupID)[1]', 'INT'));
GO

Another interesting feature of logon triggers is that in case you have a statement that generates an output like SELECT or PRINT statements it will be redirected to the error log. In the next screen capture you will see the value of the @EventData variable from the trigger printed to the errorlog.

Screen capture of the SQL Server error log.
Additional Information

Last Update: 8/2/2021

<< Previous

Next >>



Comments For This Article

















get free sql tips
agree to terms