Logon Trigger Example to Restrict Access in Sql Server

Logon Trigger Example to Restrict Access in Sql Server

In this post ,will discuss logon Triggers in SQL Server. So when does a logon trigger fire? as the name implies logon triggers fire in response to a logon even. Logon triggers fired after the authentication phase of logging and finishes.

But before the user session is actually established, logon triggers can be used for tracking login and activity, restricting logging to SQL server, and limiting the number of sessions for a specific login.

Let’s understand these logon triggers with an example

What we want to do is write a trigger that’s going to limit the maximum number of open connections for a user to 4. If an attempt is made to make a 5th connection, then we should get an error message, as you can see here,

1
So it’s the logon trigger that’s blocking this attempt to make it 5th connection, let’s see, how to write?

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 be making use of sys.dm_exec_sessions View to achieve this. Basically, sys.dm_exec_sessions View contains information about all active user connections. So if you look at view, it has got a lot of columns and we are interested in two columns. The first one is, is_user_process and second one is original_login_name.

2
So basically is_user_process column is going to tell us whether the connection is made using a user process. And the other column is original_login_name.  Original_login_name column is going to contain the login name that made the connection.

And then I’m going to order data by login time and descending order so we will get the recent successful login information.

Now I can make 4 connections, but if I try to make a fifth connection, then we want to trigger, which is going to block that 5th connection.

So now, if you look at this trigger, what is this trigger doing?

It’s rolling back. So it’s preventing the attempt to make a fifth connection. And we are printing a message.

So where is this message, this error message going in? And it will actually be returned to the error log.

Untitled

if you want to read the information from the error log, you can use this system stored procedure, sp_readerrorlog. So let’s go ahead and execute that system stored procedure.

3

Read More-

Ashok Patel

I'm an electronic engineer working in a multinational company,having good experience on Electronics and electrical engineers design and oversee production of electronic equipment such as radios, televisions, computers, washing machines and telecommunication systems.I like to do RND and Research.I also have hands on experience graphic design software and in web designing having great command on ASP.NET, HTML5, JavaScript, T-SQL, JQuery.

Add comment

Your Header Sidebar area is currently empty. Hurry up and add some widgets.