Triggers in SQL Server
Part four of a four-part series of blogs

Triggers are special stored procedures that execute automatically in response to certain events in a database. This blog explains how to create three types of trigger in SQL Server: DML, DDL and Logon triggers.

  1. Triggers in SQL Server
  2. DML Triggers in SQL Server
  3. DDL Triggers
  4. Logon Triggers (this blog)

This blog is part of our free SQL Server tutorial.

Posted by Andrew Gould on 27 November 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Logon Triggers

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

AS

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:

Logon trigger location

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:

Info on sessions

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

AS

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:

Logon error

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.

This blog has 0 threads Add post