Logon trigger limiting the number of sessions per login
Refuse a new SQL Server logon when a login has reached its session limit, with the limit read from a table.
A server-level logon trigger can refuse a new connection when a login already has too many user sessions. The limit for each login is read from a table, and logins that are not listed get a default of 200 sessions.
Create the configuration table
USE AuditDb;
go
-- Create LIMITEDLOGINS Table
CREATE TABLE dbo.limitedLogins
(
limitedLoginsId INT IDENTITY(100,1) PRIMARY KEY
,loginName VARCHAR (50) NOT NULL
,loginID VARCHAR (50) NOT NULL
,num_ALLOWED TINYINT NOT NULL
,Date_Added Date NULL DEFAULT GETDATE()
);
-- Add values to the LimitedLogins table
INSERT INTO dbo.limitedLogins
(loginName , loginID, num_ALLOWED)
VALUES
('Example User 1', 'user1','200')
,('Example User 2', 'user2','200')
,('Example User 2', 'user3','200')
;
Create the login used by the trigger
USE master
go
CREATE LOGIN app_logon_trigger WITH PASSWORD = <HASHED_PASSWORD> HASHED;
GO
USE AuditDb;
go
CREATE USER app_logon_trigger FROM LOGIN app_logon_trigger WITH DEFAULT_SCHEMA = [dbo];
GO
GRANT SELECT ON dbo.LimitedLogins TO app_logon_trigger;
GO
USE master
go
GRANT VIEW SERVER STATE TO app_logon_trigger
GO
GRANT VIEW SERVER STATE TO [Public]
GO
GRANT VIEW SERVER STATE TO [Public]
GO
Create and enable the logon trigger
USE master
GO
CREATE TRIGGER [connection_limit_trigger] ON ALL SERVER
WITH EXECUTE AS 'app_logon_trigger'
FOR LOGON
AS
BEGIN
DECLARE @maxAllowed int
DECLARE @CurrentConnection int
DECLARE @strMsg varchar(100)
SELECT @maxAllowed = NUM_ALLOWED
FROM AuditDb.dbo.LimitedLogins
WHERE loginID = ORIGINAL_LOGIN()
IF @maxAllowed IS NULL
SET @maxAllowed = 200 -- Default value
SELECT @CurrentConnection = COUNT(*)
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
AND original_login_name = ORIGINAL_LOGIN()
SET @strMsg = 'The max of connection has been reached( @CurrentConnection ' + CAST(@CurrentConnection AS varchar) + ' @maxAllowed '+ CAST (@maxAllowed AS varchar)
IF ( @maxAllowed < @CurrentConnection )
BEGIN
RAISERROR (@strMsg,16, 1 ); -- Second argument.
ROLLBACK
END
END;
GO
ENABLE TRIGGER [connection_limit_trigger] ON ALL SERVER
GO
Simpler version with one limit
CREATE TRIGGER TR_logonMax
ON ALL SERVER
FOR LOGON
AS
BEGIN
if (
SELECT COUNT(*)
FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND original_login_name = ORIGINAL_LOGIN()) > 200
ROLLBACK;
END
SELECT *
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
Useful queries
SELECT COUNT(*)
FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND original_login_name = ORIGINAL_LOGIN()
-- Scripting of existing triggers
SELECT
SSM.definition
FROM
sys.server_triggers AS ST JOIN
sys.server_sql_modules AS SSM
ON ST.object_id = SSM.object_id
Notes
- The trigger counts the user sessions of the login in
sys.dm_exec_sessions, then rolls back the logon when the count is above the limit. Check the exact boundary in a test before relying on it. - The trigger has no exemption for sysadmin members: a limit exceeded by an administrator also blocks the administrator. Keep a dedicated administrator connection available (see the article on recovering from a blocking logon trigger).
GRANT VIEW SERVER STATE TO [Public]is in the source and gives every login that right. Grant it only to the trigger's login if possible.- The password is a placeholder.
WITH PASSWORD = ... HASHEDexpects a hashed password literal, not clear text. - The "simple" version does not read the limit table and uses one limit of 200 for every login.
- Placeholders replaced: the database is
AuditDb, the trigger login isapp_logon_trigger, and the example users are generic.