Logon Trigger in SQL Server
In this article, I will discuss the Logon Trigger in SQL Server with some examples. The Logon Triggers are DDL Triggers and they are created at the Server Level. They are introduced in SQL Server 2005 SP2. At the end of this article, you will be having a very good understanding of the following things.
- What is Logon Trigger?
- Why we need Logon Trigger in SQL Server?
- How we can create a Logon Trigger?
- Examples of Logon Trigger
What is Logon Trigger in SQL Server?
The Logon trigger in SQL Server are 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 Trigger in SQL Server is commonly used to audit and control the server sessions such as
- Tracking the Login Activity
- Limiting the number of concurrent sessions for a single user
- Restricting logins to SQL Server based on time of day, hostnames, application names
Let’s understand Logon Trigger with an example.
First Login to the SQL Server in administrator mode using sa user as shown below.
The following logon trigger will limit the maximum number of open connections for a user to 2 except the sa user.
The below trigger will limit the open connections for all Logins except the ‘sa’ 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 we are creating the 3rd connection we get the below error message.
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.
It will give us the below result
To delete the Logon Trigger use the following command
DROP TRIGGER tr_Conn_Limit_LogonTriggers ON ALL SERVER
The below logon trigger blocks all the user expect 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
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
In the next article, I will discuss Logon Trigger real-time example in SQL Server.
In this article, I try to explain the Logon Trigger in SQL Server with examples. 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.