Logon Triggers in SQL Server

A logon trigger, as the name suggests, is a trigger that fires in response to a LOGON event in SQL Server.

In simple terms, a logon trigger fires whenever someone tries to establish a new connection to a database server. The trigger fires after the user authentication and the login phase completes but before the user session is actually initiated.

Advantages of Logon Triggers

There are several advantages of logon triggers:

• Tracking Login Activity
  Logon triggers can be used to keep track of the login activity taking place on the database server. For example, it can be used to maintain a list of all of the users who logged in to the database server within the specified time period.
• Restricting Number of Total Connections
  Too many database logins can affect the performance of a database, particularly when large numbers of users are accessing a database. In cases like these logon triggers can be used to place a check on the number of total logins already in existence before more are allowed.
• Restricting Number of Sessions per Connection
  A logon trigger can be used to limit the number of sessions per connection. Whenever a user tries to establish a new session, a logon trigger can check the number of sessions that are already active for that login and can restrict any new sessions if they have exceeded a limit.

Before we get started, be sure that you are fully backed up.

Accessing Active User Connections

The following script returns all the active user connections to a database.

SELECT * FROM sys.dm_exec_sessions

sys.dm_exec_sessions is a system view that returns all the active user connections. The output of the above query looks like this:

The output contains detailed information about all of the active connections. We are interested in two of those columns: is_user_process and original_login_name.
The former tells us whether the connection has been made by a user process or not, and the former contains information about the name of the connection that made the login.

Execute the following query:

SELECT is_user_process, original_login_name, *
FROM sys.dm_exec_sessions ORDER BY login_time DESC

The above query returns is_user_process and original_login_name as the first and second columns along with the rest of all the columns from the sys.dm_exec_sessions view. It sorts the results by descending order of the login time. Hence, the recent most connections appear at the top.

In the output, scroll down to the rows where the is_user_process column contains a 1.

In my case, I have two such rows as shown in the output below:

In the original_login_name column, you can see the name of the connection that made the login (In this case the name of my PC). In the program_name column, you can see the type of connection. In the above case, the connection with the session_id 51 is the connection with the SQL Server Object Explorer. The connection with the session_id 52 is the connection for the only query window in which the script is being run. If you have two query windows open, you will see a total of three user connections here, and so on.

To count the total number of connections made by user processes, execute the following query:

SELECT COUNT(*) FROM sys.dm_exec_sessions 
WHERE is_user_process = 1

The above query returns two since I have only two user_process connections on my server.

Logon Trigger for Limiting Number of Logins

Let’s create a simple logon trigger that limits the total number of logins that can be made by one connection, to 3. If the connection tries to log in to the database server for the fourth time, the logon trigger rolls back the login process.

The script for this trigger is as follows:

CREATE TRIGGER tr_CheckLogin
ON ALL SERVER
FOR LOGON
AS
BEGIN
	DECLARE @login NVARCHAR(100)
	SET @login = ORIGINAL_LOGIN()

	IF
	(SELECT COUNT(*) FROM sys.dm_exec_sessions 
	WHERE is_user_process = 1
	AND original_login_name = @login) > 3
	BEGIN
	Print 'More than three connections not allowed - Connection by ' + @login + ' Failed'
	ROLLBACK
	END

END

In the script above we create a trigger named tr_CheckLogin. The trigger scope is set to server.

You can view the trigger details by going to Object Explorer -> Server Objects -> Triggers, as shown in the figure below:

The trigger fires whenever the LOGON event takes place i.e. a connection tries to login into the server. Take a careful look at the body of the trigger.

The ORIGINAL_LOGIN function returns the name of the user process, or connection trying to login to the server. Next, the total number of connections with the same name is counted using the sys.dm_exec_session view. If the number of connections is greater than 3, the login is rolled back and a statement is printed to the user which reads: “More than three connections not allowed – Connection by ‘connection_name’ Failed”.

To verify this, open a new query window in the SQL Server management studio

Note: Previously, there are two connections open, one for SQL Server Management Studio and one for the Query window.

When you try to open a new query window, the tr_CheckLogin trigger will fire but since the total number of logins will be three at this point of time, the trigger will not roll back the login.

To verify that the total number of connection by user_process, execute the following query:

SELECT COUNT(*) FROM sys.dm_exec_sessions 
WHERE is_user_process = 1

Now, there are total 3 user connections as shown in the output:

Now again try to create a new connection by opening a new query window in SQL Server management studio.

You will see following error:

The error says that the login failed for “DESKTOP-GLQ5VRA\Mani” (the name of my PC) due to trigger execution. Basically, the trigger we created didn’t allow the connection. Wait! But where is the error message that we printed? We cannot see it here in the above message box. The custom error message that we wrote in the trigger can be viewed in the error log.

To view the error log, execute the following script:

EXECUTE sp_readerrorlog

Scroll down the error log until you find the custom message that you wrote for the logon trigger. The following screenshot shows the error log of my database server. The custom message can be seen in the error log:

Further Reading:

• YouTube: SQL Server Logon Triggers
• Microsoft.com: Logon Triggers
• SQL Studies: Escaping From A Runaway Logon Trigger