Logon Triggers in SQL Server

Logon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established. Therefore, all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log. Logon triggers do not fire if authentication fails.

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 we are referring to a nonexistent table or database will allow us 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 we are able to establish a Dedicated Administrator Connection (DAC) with SQL Server Management Studio. Because in case we have created a wrong trigger and not able to connect, then DAC connection comes for our rescue. As the DAC connection will not trigger the LOGON trigger execution and we will be able to connect to Sql Server and after connecting disable the incorrect trigger.

Uses of Logon Triggers

Ø  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

See the example

I am creating I user in sql server

CREATE LOGIN bagi_test_trigger WITH PASSWORD = 'test1234'

Now I am log on this user

  

Click on connect button.

Suppose we want to block this user between 6:00 PM and 9:00AM (None working hours). We will create a trigger which blocks this user to login the database server.

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

Trigger created successfully.

Current time

Now I am trying to logging this user.

  

Getting error like Login failed for User ‘bagi_test_trigger’ due to trigger execution.

Now I am changing the logging time

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

Now I am logging with this user.

We can drop the log on trigger

DROP TRIGGER bagi_test_trigger ON ALL SERVER

We can see the server trigger on below location.