Logon Trigger Real-Time Example in SQL Server

Logon Trigger Real-Time Example in SQL Server

In this article, I will discuss the Logon Trigger Real-Time Example in SQL Server. Please read our last article before proceeding to this article. In our previous article, we discussed the following things in details

  1. What is Logon Trigger?
  2. Why we need Logon Trigger in SQL Server?
  3. How we can create a Logon Trigger?
  4. Examples of Logon Trigger

In this article, we are going to create a database for logging the login activity for all users using a Logon Trigger.

Please use below SQL Script to create the Database, the Database Table and the Logon Trigger to audit the Logon Data.

-- Creates LogonAuditDB database for storing the audit data
CREATE DATABASE LogonAuditDB 
USE LogonAuditDB
GO

-- Creates TableAudit table for logons inside LogonAuditDB
CREATE TABLE TableLogonAudit 
(
    SessionId int,
    LogonTime datetime,
    HostName varchar(50),
    ProgramName varchar(500),
    LoginName varchar(50),
    ClientHost varchar(50)
)
GO

-- Create Logon trigger for storing the User login data
CREATE TRIGGER LogonAuditTrigger
ON ALL SERVER 
FOR LOGON
AS
BEGIN
  DECLARE @LogonTriggerData xml,
      @EventTime datetime,
      @LoginName varchar(50),
      @ClientHost varchar(50),
      @LoginType varchar(50),
      @HostName varchar(50),
      @AppName varchar(500)
 
  SET @LogonTriggerData = EventData()
 
  SET @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
  SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
  SET @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
  SET @HostName = HOST_NAME()
  SET @AppName = APP_NAME()
 
  INSERT INTO LogonAuditDB.dbo.TableLogonAudit
  ( 
    SessionId,
    LogonTime,
    HostName,
    ProgramName,
    LoginName,
    ClientHost
  )
  VALUES
  (
    @@spid,
    @EventTime,
    @HostName,
    @AppName,
    @LoginName,
    @ClientHost
  )
END
GO

You can find the Trigger inside the Triggers Folder which is inside the Server Object Folder as shown in the below image.

Logon Trigger Real-Time Example in SQL Server

The EVENTDATA() in SQL Server is an XML document that can be only available within the context of a DDL Trigger and It has the following schema

<EVENT_INSTANCE>    
  <EventType>event_type</EventType>    
  <PostTime>post_time</PostTime>    
  <SPID>spid</SPID>    
  <ServerName>server_name</ServerName>    
  <LoginName>login_name</LoginName>    
  <LoginType>login_type</LoginType>    
  <SID>sid</SID>    
  <ClientHost>client_host</ClientHost>    
  <IsPooled>is_pooled</IsPooled>    
</EVENT_INSTANCE> 

You can view the logon audit data by using the below SQL Query.

SELECT * FROM LogonAuditDB.dbo.TableLogonAudit

In the next article, I will discuss the CLR Triggers in SQL Server with some examples.

SUMMARY

In this article, I try to explain the Logon Trigger Real-Time Example in SQL Server. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *