sql-serverauthenticationtriggers

Logon trigger with both authorization and log insert


I have created a logon trigger in my SQL Server 2019 instance.

The idea is to have both:

  1. Authorization - control who can logon and who cannot. Ex by hostname, IP .... (for simplicity this below is tied to username - some must logon, some must not)
  2. Log record on each case, authorized to enter, and not authorized

Sample trigger is:

CREATE OR ALTER TRIGGER [trigger_name]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    BEGIN TRAN
        INSERT INTO LOG_TABLE ...

    COMMIT TRAN

    IF ORIGINAl_LOGIN() = 'TEST2' 
    BEGIN
        ROLLBACK;
    END;
END

I test connecting with users TEST1 and TEST2.

Test:

Problem: no log written for TEST2


Solution

  • You can write the log after you rollback the trigger's transaction. EG

    CREATE OR ALTER TRIGGER [only_sa_allowed]
    ON ALL SERVER WITH EXECUTE AS 'sa'
    FOR LOGON
    AS
    BEGIN
    
        IF ORIGINAl_LOGIN() <> 'sa' 
        BEGIN
            ROLLBACK;
        END;
        INSERT INTO LOG_TABLE(user_name) values (ORIGINAL_LOGIN());
    END