BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
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.
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
--Your trigger code here
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
IF ORIGINAL_LOGIN() = 'WiseOwl' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'WiseOwl') > 3
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.