SQL- Logon Triggers

LOGON Triggers are another beautiful feature of SQL. It is used to track the audit and control server sessions, such as by tracking login activity, restricting logins to SQL Server, or limiting the number of sessions for a specific login means maintain the log in attempts to SQL Server initiated by a particular login. We should need to be careful when setting them up because it can be easily locked users out of the server and / or cause other problems.

Similar to DDL Triggers, LOGON Triggers fire stored procedures or T-SQL statements in response to LOGON events.  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. Logon triggers do not fire if authentication fails.

LOGON event corresponds to the AUDIT_LOGIN SQL Trace event, which can be used in Event Notifications. The primary difference between triggers and event notifications is that triggers are raised synchronously with events, whereas event notifications are asynchronous, if you want to stop a session from being established, you must use a logon trigger. An event notification on an AUDIT_LOGIN event cannot be used for this purpose.

For example, in the following code, the logon trigger denies log in attempts to SQL Server initiated by login msbi_datatools if there are already three user sessions created by that login:

USE master; GO -- create the new login CREATE LOGIN msbi_datatools WITH PASSWORD = 'M@c123' MUST_CHANGE,     CHECK_EXPIRATION = ON; GO -- Grant permission GRANT VIEW SERVER STATE TO msbi_datatools ; GO -- create trigger to fire when connection limits meet the condition CREATE TRIGGER LogonTrigger_ConnectionLimit ON ALL SERVER WITH EXECUTE AS 'msbi_datatools' FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'msbi_datatools' AND (SELECT COUNT(*) FROM sys.dm_exec_sessions             WHERE is_user_process = 1 AND original_login_name = 'msbi_datatools') > 3             Begin                                                 Print 'You are logged more than authorised sessions' ;                                                       ROLLBACK;                                                 End END;

You can easily associate multiple triggers on the LOGON event and any one of these triggers can be designated the first or last trigger to be fired on an event by using the sp_settriggerorder system stored procedure.  You should keep in mind that SQL Server does not guarantee the execution order of the remaining triggers.

Use msdb Go -- create CaptureLogin Details to store every login information CREATE TABLE CaptureLoginDetails (LogonTime datetime, LoginName varchar(50), ClientHost varchar(50), LoginType varchar(50) ) -- Logon Trigger to fill the audit log table CREATE TRIGGER LogonTrigger_CaptureLogin ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN -- declare local variable to store value declare @LogonTriggerData xml, @EventTime datetime, @LoginName varchar(50), @HostName varchar(50), @LoginType varchar(50) -- set value from the event data      set @LogonTriggerData = eventdata() set @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') set @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)') set @HostName = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)') set @LoginType = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(50)') -- check condition here if @LoginType = 'SQL Login' and @HostName not in ('sa,') and @LoginName not in ('sa')   Begin    -- fill the data into logon capture detail table   Insert into Msdb..CaptureLoginDetails (LogonTime,LoginName,ClientHost,LoginType)   Values(@EventTime,@LoginName,@HostName,@LoginType)   End               end

Managing Transactions

The life-cycle of a logon trigger is very simple: a user connects to SQL Server, the Logon trigger fires; an implicit transaction is opened and if you want to deny the attempt to log in to SQL Server, just issue a ROLLBACK statement and life-cycle is completed. This involve the following steps-

• Before SQL Server fires a logon trigger, SQL Server creates an implicit transaction that is independent from any user transaction.
• Whenever the first logon trigger starts firing, the transaction count is 1. After all the logon triggers finish executing, the transaction commits. As with other types of triggers, SQL Server returns an error if a logon trigger finishes execution with a transaction count of 0.
• The ROLLBACK TRANSACTION statement resets the transaction count to 0, even if the statement is issued inside a nested transaction. COMMIT TRANSACTION might decrements the transaction count to 0. 
• In the real practice, avoid using COMMIT TRANSACTION statements inside logon triggers.

A logon trigger can effectively prevent successful connections to the Database Engine for all users, including members of the sysadmin fixed server role. When a logon trigger is preventing connections, members of the sysadmin fixed server role can connect by using the dedicated administrator connection, or by starting the Database Engine in minimal configuration mode.

Click on Triggers to know more.

Click to know more about the difference between Triggers and Stored Procedures.

Click on Benefits of Triggers to know more.

Click on Disadvantages of Triggers to know more.

Click on SQL - DML Triggers to know more.

Click on SQL - DDL Triggers to know more.

References:

https://technet.microsoft.com/en-us/library/bb326598(v=sql.130).aspx