SQL Server Logon Trigger Examples

By: Bhavesh Patel   |   Updated: 2019-07-26   |   Comments (5)   |   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".

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.

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

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". 

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.

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

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".

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.

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

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.

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.

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.

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

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

• Kindly run in test server before rolling-out in production.
• Check out SQL Server Trigger Tips.
• Check out logon trigger in SQL Server.
• Grant server permissions in SQL Server.

About the author

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