LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon