Logon trigger is basically used for auditing, and controlling logins or sessions on SQL Server.
But it could be used for several purposes like :
- Deny access for particular client application(eg. Clients connecting from ODBC , OLEdb etc.)
- 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.