Logon Trigger Example to Restrict Access In Sql Server

In this post, we will discuss logon triggers in SQL Server. Logon triggers are fired in response to a logon event, as the name implies. They are activated after the authentication phase of logging in has completed, but before the user session is established.

Logon triggers can be utilized for various purposes, such as tracking login activity, restricting logins to SQL Server, and limiting the number of sessions for a specific login.

Understand logon triggers with an example

We aim to create a trigger that restricts the maximum number of concurrent connections for a user to 4. If an attempt is made to establish a fifth connection, an error message should be generated.

So it's the logon trigger that's preventing this attempt to establish the fifth connection. Let's see how to write it.

CREATE TRIGGER tr_LogonRestrictConnectionTriggers
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) > 4
BEGIN
Print 'Third connection of ' + @LoginName + ' blocked'
ROLLBACK
END
END

We will utilize the sys.dm_exec_sessions view to accomplish this task. Essentially, the sys.dm_exec_sessions view contains information about all active user connections. Upon examining the view, we find numerous columns, but we are particularly interested in two columns: is_user_process and original_login_name.

So basically, the is_user_process column will indicate whether the connection is made using a user process. The other column, original_login_name, will contain the login name that made the connection.

Next, I'll order the data by login time in descending order so that we obtain the most recent successful login information.

Now, users are allowed to establish four connections, but if an attempt is made to establish a fifth connection, we want the trigger to block that fifth connection.

So, if we look at this trigger, what is it doing? It's rolling back, thereby preventing the attempt to establish a fifth connection. Additionally, we're printing a message.

Now, where does this message, this error message, go? It will be returned to the error log.

If you wish to read the information from the error log, you can utilize the system stored procedure called sp_readerrorlog. Let's proceed and execute that system stored procedure.

• DML triggers ,After Insert,After Delete Trigger with examples
• After Update Trigger in Sql Server With Example
• Instead Of Insert Triggers in Sql server With Example
• Instead Of Update Trigger In Sql Server With Example