This page has 0 threads | Add post

Logon Triggers

Written by Andrew Gould

In this tutorial

Basic Syntax of Logon Triggers
When do Logon Triggers Fire?
Getting Information about User Sessions
An Example to Prevent Users Establishing More Sessions

While DML and DDL triggers work based on changes to the data or objects in a database, logon triggers detect when users log on to the server and execute stored procedures in response to this. Typical uses for logon triggers include keeping track of user activity, restricting access to SQL Server and maintaining a limit to the number of concurrent users.

Basic Syntax of Logon Triggers

The basic syntax for a logon trigger is shown in the example below:

CREATE TRIGGER myLogonTrigger

ON ALL SERVER

FOR LOGON

BEGIN

--Your trigger code here

END

You can find logon triggers in the same place as server-scoped DDL triggers, as shown in the diagram below:

Expand the Server Objects folder followed by the Triggers folder to find your Logon triggers.

You can drop, alter, disable and enable logon triggers using the same methods as for other types of trigger. You can also change the order in which logon triggers fire using the sp_settriggerorder system stored procedure.

When do Logon Triggers Fire?

The answer to this question seems obvious: when a user logs on! It's worth considering exactly what this means in terms of what you can do within a logon trigger. According to Microsoft's TechNet site a logon trigger fires when the authentication stage of logging on has finished but before the user session is actually established. This means that if your trigger is designed to show messages to the user (using the PRINT statement for example) the user won't ever see them.

Getting Information about User Sessions

To make effective use of logon triggers it helps to be able to find information on the sessions that are currently established. The example code below shows how to do this:

--Return data on the current sessions

SELECT * FROM sys.dm_exec_sessions

An example of the output from this query is shown in the diagram below:

There are many more columns than the ones shown here

Two of the more useful columns from this output are login_name and is_user_process.

An Example to Prevent Users Establishing More Sessions

The example shown below prevents a certain user from establishing any more connections to the server when they already have three connections open.

CREATE TRIGGER trgNoMoreConnections

ON ALL SERVER

FOR LOGON

BEGIN

IF ORIGINAL_LOGIN() = 'WiseOwl' AND

(SELECT COUNT(*) FROM sys.dm_exec_sessions

WHERE is_user_process = 1 AND

original_login_name = 'WiseOwl') > 3

ROLLBACK

END

The ORIGINAL_LOGIN function shown in the code above returns the name of the user who is logging in. In the WHERE clause of the query we're testing if the is_user_process column contains a value of 1 (i.e. it is a user process), and if the original_login_name column is equal to the user whose sessions we're trying to limit.

The result of attempting to open a new session when the user already has three open is an error message similar to the one shown below:

This is part of the error message that will appear.

Take care when creating logon triggers as you can fairly easily find yourself unable to connect to a SQL Server instance! If this happens you'll need somebody who is part of the sysadmin role to connect and disable or delete the trigger.

Previous
tutorial

Pivoting data

Previous
part

DDL Triggers

Top of page

Next
part

Next
tutorial

Dynamic SQL

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Triggers

Logon Triggers

Basic Syntax of Logon Triggers

When do Logon Triggers Fire?

Getting Information about User Sessions

An Example to Prevent Users Establishing More Sessions

Head office

London

Manchester