What is Logon Trigger?

Logon Triggers are special kind of Stored Procedure or an operation that gets executed automatically in response to a LOGON event. They get executed only after the successful authentication but before the user session is established. If authentication fails the logon triggers will not be fired.

Caution! : Logon Triggers are very useful feature, but a small mistake in the Logon Trigger may cause database server un-accessible to any user including SA user. For example in a Logon trigger you are referring to a non existent table or database will allow you to create it. But once it is created then no one will be able to connect to the server as Logon trigger errors out. So it is best advised to check whether you are able to establish a Dedicated Administrator Connection (DAC) with SQL Server Management Studio. Because in case you have created a wrong trigger and not able to to connect, then DAC connection comes for your rescue. As the DAC connection will not trigger the LOGON trigger execution and you will be able to connect to Sql Server and after connecting disable the incorrect trigger.

Uses of Logon Triggers

Below are the some of the usecase scenarios where LOGON triggers will be usefull:

• To audit and control server sessions
• Restricting logins to Sql Server
• Restricting the number of sessions for a specific login
• Restricting user from logging-in outside permitted hours

Understanding Logon Triggers with extensive list of examples

Create a login TestUser by executing the following script:

-- Create a login 
CREATE LOGIN TestUser WITH PASSWORD = 'TestPass1'

[ALSO READ] Data Manipulation Language (DML) Triggers

EXAMPLE 1: This example Logon trigger blocks the user TestUser from connecting to Sql Server after office hours.

Execute the following script to create a Logon trigger which blocks the TestUser user from connecting to Sql Server after office hours

CREATE TRIGGER LimitConnectionAfterOfficeHours
ON ALL SERVER FOR LOGON 
AS
BEGIN
 IF ORIGINAL_LOGIN() = 'TestUser' AND
  (DATEPART(HOUR, GETDATE()) < 9 OR 
                  DATEPART (HOUR, GETDATE()) > 18)
 BEGIN
  PRINT 'You are not authorized to login after office hours'
  ROLLBACK
 END
END

Now try to connect to Sql Server using the TestUser user after office hours

From the above result we can see that the Logon Trigger LimitConnectionAfterOfficeHours is blocking the TestUser from connecting to Sql Server after office hours

In case of Logon Triggers the PRINT statement result in the trigger will be logged to the Sql Server Error Log. Let us login as different user and check whether PRINT message in the Logon trigger is logged into the Error Log during the above failure attempt:

Drop the above Logon Trigger by executing the following statement:

DROP TRIGGER LimitConnectionAfterOfficeHours ON ALL SERVER

[ALSO READ] Data Definition Language (DDL) Triggers

EXAMPLE 2: This example Logon Trigger blocks a user from establishing more than two user sessions at any given point of time

Execute the following script to create a Logon Trigger which blocks the TestUser from establishing more than two user sessions at any given point of time

CREATE TRIGGER LimitMultipleConcurrentConnection
ON ALL SERVER WITH EXECUTE AS 'sa' 
FOR LOGON
AS
BEGIN
 IF ORIGINAL_LOGIN() = 'TestUser' AND
  (SELECT COUNT(*) FROM   sys.dm_exec_sessions
   WHERE  Is_User_Process = 1 
    AND Original_Login_Name = 'TestUser') > 2
 BEGIN
  PRINT 'You are not authorized to login, as you already have two active user sessions'
  ROLLBACK
 END
END

Now try to establish more than two concurrent sessions for the TestUser:

From the above image we can see that the TestUser was able to have two concurrent new query sessions, but the attempt to open the third session is blocked by the trigger.

Below is the error logged in the Sql Server Error Log:

In the above example trigger you can observe that, during trigger creation I have added the clause EXECUTE AS ‘sa’. The reason for this is only the users with VIEW SERVER STATE permission on the server can see all the executing session on the instance of Sql Server. And the users who don’t have this permission can see only the current session. Because of this I am executing the above trigger as ‘sa’. An alternative option for this is grant TestUser the VIEW SERVER STATE permission using GRANT VIEW SERVER STATE TO TestUser statement.

Drop the above Logon Trigger by executing the following statement:

DROP TRIGGER LimitMultipleConcurrentConnection ON ALL SERVER

[ALSO READ]
Introduction to Triggers
Data Manipulation Language (DML) Triggers
Data Definition Language (DDL) Triggers
INSERTED and DELETED Logical Tables