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..
1,914 total views, 1 views today