Logon Triggers in SQL Server

sql server logon trigger example
sql server logon trigger audit

In this video we will discuss Logon triggers in SQL Server.

As the name implies Logon triggers fire in response to a LOGON event. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established.

Logon triggers can be used for
1. Tracking login activity
2. Restricting logins to SQL Server
3. Limiting the number of sessions for a specific login

Logon trigger example : The following trigger limits the maximum number of open connections for a user to 3.

CREATE TRIGGER tr_LogonAuditTriggers
ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @LoginName NVARCHAR(100)

Set @LoginName = ORIGINAL_LOGIN()

IF (SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1
AND original_login_name = @LoginName) ] 3
BEGIN
Print ‘Fourth connection of ‘ + @LoginName + ‘ blocked’
ROLLBACK
END
END

An attempt to make a fourth connection, will be blocked. The trigger error message will be written to the error log. Execute the following command to read the error log.
Execute sp_readerrorlog

Text version of the video
http://csharp-video-tutorials.blogspot.com/2015/09/logon-triggers-in-sql-server.html

Slides
http://csharp-video-tutorials.blogspot.com/2015/09/logon-triggers-in-sql-server_13.html

All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html

All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists