SQL LOGON Trigger

LOGON Trigger fires in response to LOGON events of SQL Server is raised.

A LOGON Triggers can be useful in many ways, such as to restrict number of connection made by a Login or to prevent a user from logging-in outside .

For example, you can create a LOGON Trigger to restrict a user from simultaneously opening more than one connections with the SQL Server.

CREATE TRIGGER restrict_sqlsession
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN() <> 'sa'
AND
( SELECT COUNT(*)
FROM sys.dm_exec_sessions
WHERE Is_User_Process = 1 AND
Original_Login_Name = ORIGINAL_LOGIN()
) > 2
ROLLBACK
END

Now if user try to access more than one session in SQL Server, it returns an error as shown below.

 

Also Read..

DDL TRIGGER

LOGON Trigger