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.