How to Create LOGON Trigger in SQL Server

Most recently I had to create a SQL Logon trigger that set a limit to sessions a user could open in SQL Server.

For example, I log into SSMS and cannot open more than 10 sessions. This logon trigger will enforce that I don’t exceed 10 concurrent sessions.

The script below is what I came up with. It’s fairly straight-forward. At the bottom of the query you will see 2 SELECT statements:

1. sys.triggers – You will find database level triggers here.
2. sys.server_triggers – You will find server level triggers here.

Feel free to modify the script below to suit your specific needs.

CREATE TRIGGER limit_sessions_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @ErrorText varchar(128)

SET @ErrorText = 'Cannot exceed 20 sessions. Close out older sessions to open newer ones.
    				If you need help, contact your Database Admin.';

IF ORIGINAL_LOGIN()= 'JohnDoe' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'JohnDoe') > 20
	BEGIN
		PRINT @ErrorText
		ROLLBACK;
	END
END;
GO

--Instance/Server level Triggers
SELECT *
FROM sys.server_triggers


--Database Level Triggers
SELECT *
FROM sys.triggers

--Delete Server level Trigger
DROP TRIGGER limit_sessions_trigger
ON ALL SERVER;
GO