Logon Triggers in SQL Server

Logon Triggers in SQL Server with Examples

In this article, I am going to discuss the Logon Triggers in SQL Server with examples. Please read our previous article where we discussed how to create and manage users in SQL Server with examples. As part of this article, we are going to discuss the following pointers in detail.

  1. What are Logon Triggers in SQL Server?
  2. Why we need Logon Triggers?
  3. How we can create a Logon Trigger?
  4. Real-time Examples of Logon Trigger

Note: The Logon Triggers are DDL Triggers and they are created at the Server Level. They are introduced in SQL Server 2005 SP2.

What is Logon Trigger in SQL Server?

The Logon Triggers in SQL Server are the special kind of stored procedure or we can also say a special type of operation which fire or executed automatically in response to a LOGON event and moreover, we can define more than one Logon trigger on the server.

The Logon triggers are fired only after the successful authentication but before the user session is actually established. If the authentication is failed then the logon triggers will not be fired.

Why we need Logon Trigger in SQL Server?

The Logon Triggers in SQL Server are commonly used to audit and control the server sessions such as

  1. Tracking the Login Activity
  2. Limiting the number of concurrent sessions for a single user
  3. Restricting logins to SQL Server based on time of day, hostnames, application names
Understanding the Logon Triggers with an example.

First, log in to the SQL Server in administrator mode using admin user as shown below.

Logon Triggers in SQL Server

Creating Logon Trigger in SQL Server:

The following logon trigger will limit the maximum number of open connections for a user to 2 except the sa user. That means the following logon trigger will limit the open connections for all Logins except the ‘sa’ user once the open connection limit is reached to 2, then the user will not be able to create a new connection. Instead, the user will get an error message.

CREATE TRIGGER tr_Conn_Limit_LogonTriggers
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
  DECLARE @LoginName NVARCHAR(100)
    	SET @LoginName = ORIGINAL_LOGIN()

  IF	@LoginName <> 'sa'
       AND
       (	SELECT COUNT(*)
      FROM   sys.dm_exec_sessions
            WHERE  Is_User_Process = 1 AND
      Original_Login_Name = @LoginName
       ) > 2
  BEGIN
    PRINT 'Third session for the user ' + @LoginName + ' is blocked'
    ROLLBACK
  END
END

So let’s login with the testuser user and try to open more than 3 connections and while you are creating the 3rd connection we get the below error message.

Logon Trigger in SQL Server

But you can open as many connections as you want using the sa user as there is no restriction for the sa user with the logon trigger. You can find the trigger error messages which will be written to the error log by executing the following command.

Execute sp_readerrorlog 

It will give the following output.

Logon Trigger in SQL Server

To delete the Logon Trigger use the following command

DROP TRIGGER tr_Conn_Limit_LogonTriggers ON ALL SERVER

Example2: 

The following logon trigger will block all the users except the sa user from connecting to SQL Server after office hours.

CREATE TRIGGER tr_Limit_Connection_After_Office_Hours
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
  DECLARE @LoginName NVARCHAR(100)
        SET @LoginName = ORIGINAL_LOGIN()

  IF @LoginName <> 'sa' AND
      (DATEPART(HOUR, GETDATE()) < 9 OR
                  DATEPART (HOUR, GETDATE()) > 18)
  BEGIN
    PRINT 'You are not authorized to login after office hours'
    ROLLBACK
  END
END
Example3:

Create a logon trigger that only allows only the whitelisted hostnames to connect to the server.

CREATE TRIGGER tr_Restrictied_Host_Only
ON ALL SERVER
FOR LOGON
AS
BEGIN
    IF
    (
        -- White list of allowed hostnames are defined here.
        HOST_NAME() NOT IN ('DevHost','QAHost','UATHost','ProdHost')
    )
    BEGIN
        PRINT 'You are not allowed to login from this hostname.'
        ROLLBACK;
    END 
END
Logon Trigger Real-Time Example:

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 in SQL Server with Examples. I hope you enjoy this article. I would like to have your feedback. Please post your feedback, question, or comments about this article.

1 thought on “Logon Triggers in SQL Server”

Leave a Reply

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