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.

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.