Logon trigger is basically used for auditing, and controlling logins or sessions on SQL Server.

But it could be used for several purposes like :

  1. Deny access for particular client application(eg. Clients connecting from ODBC , OLEdb etc.)
  2. Allow login for certain time period in a day.

Tracking login activity

And many more.

Creating a LOGON trigger

Here in following code snippet I have created a simple Logon trigger for : If someone is connecting with SQL Server from any other client application a part form SSMS(SQL Server Management Studio.)
Will get an Error: “Logon failed for sa due to trigger execution.”

CREATE TRIGGER [DenyAccess]

ON ALL SERVER WITH EXECUTE AS 'sa'

FOR LOGON

AS

BEGIN

DECLARE @data XML

SET @data = EVENTDATA()

DECLARE @AppName sysname

SELECT @AppName = [program_name]

FROM sys.dm_exec_sessions

WHERE session_id = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')

IF (@AppName not in ('Microsoft SQL Server Management Studio') )

begin

RAISERROR (15600,-1,-1, 'Can not access form ODBC') WITH LOG,NOWAIT

ROLLBACK; --Disconnect the session

End;

End;

Uses

I have described simple example here for better understanding. You can use LOGON trigger according to your need (ex. You can deny access based on host-name, ip address range.. etc.)

You can also write all these failure and success event in DB.