SQL Server Logon Trigger Examples

By:   |   Comments (4)   |   Related: > Triggers


Problem

Triggers are database objects that store code and get executed automatically when some type of event occurs, such as a data change or change to an object. In SQL Server, there are four types of triggers: DDL Triggers (Data Definition Language), DML Triggers (Data Manipulation Language, CLR Triggers, and Logon Triggers. In this article we will learn about logon triggers and use cases.

Solution

In order to respond to logon events, logon triggers are executed automatically whenever a logon occurs. It executes after authentication is made successfully and before the user session is established. This user session is not established when the transaction fails or is rolled back or an error is generated from the trigger with a severity of 20 or greater. In authentication fails, the logon trigger will not be fired. For the logon event, SQL Server creates an implicit transaction which is independent of any user transaction. The transaction count is 1 when the logon trigger is fired and SQL Server returns an exception if a logon trigger finishes execution with a transaction count of 0.

Definition of a SQL Server Logon Trigger

A Logon trigger is fired in response to a logon event that is raised when a user session is being established. Here is the basic syntax to create a logon trigger using T-SQL.  Between the BEGIN and END we add the logic that needs to be checked when a logon occurs.

CREATE OR ALTER TRIGGER [Name of Trigger]
ON ALL SERVER 
FOR LOGON
AS
BEGIN
   -------------
END

These are some use cases where a logon trigger is useful and we cover these below.

  • Restrict users to only login at certain times
  • Restrict number of concurrent sessions for a specific login
  • Restrict total number of connections
  • Restrict login by Hostname or IP Address
  • Use a logon trigger for auditing

SQL Server Logon Trigger to restrict logon to certain times

Using a logon trigger, we can restrict a user connecting to SQL Server for a certain time frame.

In this example, I want to restrict login "testuser" to only connect to SQL Server between 10am and 6pm.

First, I am going to create the SQL Login "testuser".

Create a login testuser

I am going to apply the view server state permission to this login that enables a login to view the results of Dynamic Management Objects.

Applied View Server state permission to login testuser

I am going to create logon trigger "Logon_In_TimeSlot" as follows.

Create a trigger "Logon_In_TimeSlot"

In this trigger, I have added two condition.

  • ORIGINAl_LOGIN () will return the value of the login which made the original connection to SQL Server. I want to restrict login "testuser", so I added another condition on the "testuser" login.
  • In addition, I restricted the specific login "testuser" to connect to SQL Server between 10am and 6pm. When testuser tries to login any time except between 10am – 6pm then the user session will be rolled back.

On my machine, the time is 08:45am and I am going to try login with the testuser login.  We can see, I am not able to connect and get the error "Logon failed for login 'testuser' due to trigger execution". 

For Trigger testing, Login by user testuser by out of business hours.

Restrict number of concurrent sessions for a specific login with a SQL Server Logon Trigger

A logon trigger can also be used to limit the number of sessions per connection. When a user tries to establish to a new session, a logon trigger can check the number of sessions that are already active for that login and restrict a new session if it exceeds the limit.

For this demonstration I am going to create a logon trigger "Logon_restrict_concurrent_user_sessions" and also DROP the previous trigger "Logon_In_TimeSlot". This will restring the login "testuser" to 5 connections.  I am also adding a PRINT statement to provide more information about the failure.

Create a trigger for restrict concurrent User sessions

Testing the trigger, we can see below the logon was rolled back.

For Trigger testing, login by user and added sessions in SSMS

If I look at the contents of the SQL Server Error Log, I can see the error message which was printed from the trigger "More than five connections are not allowed – Connection by this testuser Failed".

Check the Error logs.

Restrict total number of SQL Server connections with a SQL Server Logon Trigger

I need to restrict the overall number of connections to a SQL Server instance and can use a logon trigger to do this. For this test I dropped the other trigger and created the new one below.  This will restrict the overall number of connections to 5.

Create a trigger for total connections

For this this, I had a few connections open and then tried to login in with sa and got the following error.

For trigger testing try for login by SA.

I also found the error in the SQL Server Error Log "This server More than five total connections are not allowed" as expected.

Restrict login by Hostname or IP Address with a SQL Server Logon Trigger

You can get more details about the connection using the function EVENTDATA() and use this in the logon trigger.  This function returns information about the server or database events. The data is in an XML format, so we need to read the XML to get the values. I can get event type, post time, SPID, server name, login name, login type, SID, client host, is pooled from this action. Using client host, we can get the hostname and IP address as well.

 For this demonstration this, I am going to create a configuration table ValidIPAddressRange that holds the valid IPs we want to allow access and just add the local machine as the old valid machine that can connect.  You can add additional items as needed.

Create a central table for a validate IP range

This creates a logon boundary table, so whenever there is a new connection this is checked. If value range is not found in this table then the connection will be discarded. Beware, if the table is empty then you won't be able to connect to SQL Server.

After dropping the other trigger, I created the following logon trigger to restrict based on IP.

Create Trigger for testing IP range.

Use a SQL Server Logon Trigger for Auditing

If somebody has accessed SQL Server, we can capture the login detail with a logon trigger and save the details to an audit table.

Here I am going to create an audit table LogonAudit and create trigger LogonTrigger_For_Audit, which is captures logon events into this table.

Create a trigger for auditing purpose..

Below we can see the data that was captured.  You can extend this further and capture additional details to meet your requirements.

sql query

Be careful when testing SQL Server Logon Triggers

  • In my demonstrations, I removed any existing logon trigger before creating a new trigger. If there are multiple, each will fire.
  • When creating a logon trigger, don't just copy and paste the SQL code. First make sure you understand what it is doing so you don't lock yourself and other users out of the system.
  • Always try to leave a provision in the code, so the SA login can always connect.
  • When you put limits in the code, try to use a large range when testing.
  • First try to use in a testing environment before rolling out to production.
  • If you unable to login using SSMS due to trigger execution, you can use the SQLCMD utility to connect.

You can also disable all logon triggers using the command "DISABLE TRIGGER ALL ON SERVER".

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Bhavesh Patel Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Saturday, September 21, 2019 - 8:50:05 AM - Bhavesh Patel Back To Top (82532)

Hi follow the instruction which I mentioned in the last step

If you are unable to log in using SSMS due to trigger execution, you can use the SQLCMD utility to connect.

You can also disable all logon triggers using the command "DISABLE TRIGGER ALL ON SERVER".

Saturday, September 21, 2019 - 8:48:08 AM - Bhavesh Patel Back To Top (82531)

Hi Nazrul

please remove the login condition from the IF condition and add this condition in the query as per my example then it should work.

Tuesday, September 17, 2019 - 12:24:08 PM - Nazrul Back To Top (82484)

Can I user Logon trigger to limit two types of users in two set of rules, for example Admin can make up to 5 connection and non-admin can make upto 3 connection? Here is what came up with but it doesnt work. I appreciate your help.

USE master;
GO

ALTER TRIGGER connection_limit_trigger_SSM
ON ALL SERVER
FOR LOGON
AS
BEGIN
  --DECLARE @loginName varchar(25)
  --SET @loginName = ORIGINAL_LOGIN()
  BEGIN
    IF ORIGINAL_LOGIN() IN ('admin1', 'admin2')
    and (SELECT COUNT(*) FROM sys.dm_exec_sessions
         WHERE is_user_process = 1 AND original_login_name = ORIGINAL_LOGIN()) > 10
    BEGIN
      ROLLBACK
    END
    ELSE
    BEGIN
      IF ORIGINAL_LOGIN() IN ('USER1', 'USER2')
      and (SELECT COUNT(*) FROM sys.dm_exec_sessions
           WHERE is_user_process = 1 AND original_login_name = ORIGINAL_LOGIN()) > 2
      BEGIN
        ROLLBACK
      END
    END
  END
END

Friday, August 16, 2019 - 1:19:25 AM - Grzegorz Lyp Back To Top (82073)

Use try..catch together with dynamic SQL because when the user tables used in logon triggers are dropped then nobody would log on.














get free sql tips
agree to terms