1. Logon triggers
In this lesson, you will learn about a special type of trigger called the logon trigger.
2. Definition and properties
Like other triggers, logon triggers perform a set of actions when fired.
Their defining characteristic is that they are fired by LOGON events.
A LOGON event occurs when a user logs on and creates a connection to SQL Server.
The trigger is fired after the authentication phase (meaning after the username and password are checked), but before the user session is established (when the information from SQL Server becomes available for queries).
This is important to know when using these triggers to audit and control server sessions.
3. Logon trigger prerequisites
A logon trigger can only be attached at the server level, and the firing event can only be LOGON.
When you create a logon trigger, you define the set of actions to be performed by the trigger when it's fired. We will just audit the logon sessions in this example, and we'll use "LogonAudit" as the trigger name.
Now let's see how to create the trigger.
4. Logon trigger definition
We start with the same syntax we used for the other triggers and pass in the name we chose.
Logon triggers are attached at the server level, so we use the ALL SERVER syntax.
When the event that fires the trigger starts, the trigger will be executed under the same credentials (username and password) as the firing event.
Regular users don't usually have access to sensitive information like logon details, so we instruct the trigger to run under the "sa" account.
This is a built-in administrator account that has full permissions on the server; running it under this account ensures that the trigger will be able to extract information about the logon details.
This information will be stored into the "ServerLogonLog" table.
5. Logon trigger definition summary
To summarize, we are creating a trigger called "LogonAudit" at the server level.
To avoid permission issues, the trigger will be executed as the "sa" account with administrator privileges.
The trigger will be fired for LOGON events and it will store details about the user connecting to SQL Server into the "ServerLogonLog" table.
6. Let's practice!
You'll find dealing with logon triggers much easier with some hands-on experience. Let's practice!