Auditing SQL Server using LOGON Trigger

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