Logon Trigger Real-Time Example in SQL Server

Logon Trigger Real-Time Example in SQL Server

In this article, I am going to discuss the Logon Trigger Real-Time Example in SQL Server. Please read our previous article before proceeding to this article where we discussed the basics of Logon Trigger in SQL Server. 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 am going to discuss the CLR Triggers in SQL Server with some examples. Here, 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 needs. 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 *