Logon triggers

Building and Optimizing Triggers in SQL Server

Florin Angelescu

Instructor

Definition and properties

  • Performs a set of actions when fired
  • The actions are performed for LOGON events
  • After authentication phase, but before the session establishment
Building and Optimizing Triggers in SQL Server

Logon trigger prerequisites

  • Trigger firing event
  • Description of the trigger
  • Trigger name
  • LOGON
  • Audit successful / failed logons to the server
  • LogonAudit
Building and Optimizing Triggers in SQL Server

Logon trigger definition

CREATE TRIGGER LogonAudit

ON ALL SERVER WITH EXECUTE AS 'sa'

FOR LOGON

AS
    INSERT INTO ServerLogonLog
                        (LoginName, LoginDate, SessionID, SourceIPAddress)
    SELECT ORIGINAL_LOGIN(), GETDATE(), @@SPID, client_net_address
    FROM SYS.DM_EXEC_CONNECTIONS WHERE session_id = @@SPID;
Building and Optimizing Triggers in SQL Server

Logon trigger definition summary

CREATE TRIGGER LogonAudit
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
    INSERT INTO ServerLogonLog
                        (LoginName, LoginDate, SessionID, SourceIPAddress)
    SELECT ORIGINAL_LOGIN(), GETDATE(), @@SPID, client_net_address
    FROM SYS.DM_EXEC_CONNECTIONS WHERE session_id = @@SPID;
Building and Optimizing Triggers in SQL Server

Let's practice!

Building and Optimizing Triggers in SQL Server

Preparing Video For Download...