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:
- sys.triggers – You will find database level triggers here.
- 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