LOGON Triggers in SQL Server

In SQL Server, the Logon trigger is fired automatically on a LOGON event. They are DDL triggers and are created at the server level. We can define more than one LOGON trigger on a server.

A LOGON trigger can be used in controlling server sessions by tracking login activity, restricting logins to the SQL Server, or limiting the number of sessions for a particular login.

The following LOGON trigger restricts the login attempt to SQL Server by sa login if there are already two user sessions created by that login.

Example: Create LOGON Trigger

CREATE TRIGGER trgLoginConnection 
ON ALL SERVER WITH EXECUTE AS N'sa'  
FOR LOGON  
AS  
BEGIN  
IF ORIGINAL_LOGIN() = N'sa' AND  
    (SELECT COUNT(*) FROM sys.dm_exec_sessions  
            WHERE is_user_process = 1 AND  
                original_login_name = N'sa') > 2  
			ROLLBACK;  
END;

All the LOGON triggers are created under the Server Objects -> Triggers folder, as shown below.