In SQL Server, you can use a logon trigger to audit and control server sessions, such as track login activity, restrict logins to SQL Server, or limit the number of sessions for a specific login.

This article provides an example of using a logon trigger to limit the number of simultaneous sessions for a specific login.

Example

Here’s an example of a logon trigger that limits the number of simultaneous sessions for a specific login to 1.

Create a login:

CREATE LOGIN Marge WITH PASSWORD = 'SQLServer123';
GRANT VIEW SERVER STATE TO Marge;

Create the logon trigger:

CREATE TRIGGER trg_limit_concurrent_sessions
ON ALL SERVER WITH EXECUTE AS 'Marge'
FOR LOGON  
AS  
BEGIN  
IF ORIGINAL_LOGIN() = 'Marge' AND  
    (SELECT COUNT(*) FROM sys.dm_exec_sessions  
            WHERE is_user_process = 1 AND  
                original_login_name = 'Marge') > 1
    ROLLBACK;  
END;

Now, when that user tries to log in on a second connection, they should get the following error:

Error message: Logon failed for login 'Marge' due to trigger execution.
Changed database context to 'master'.
Changed language setting to us_english.

Note that all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log.

Also, logon triggers do not fire if authentication fails.