Earlier this week, I chose a few SQL Server 2008 topics for our #SQLHelp MCITP study group to discuss here on the blog.  Thus, today I will discuss logon triggers, one of the new features available in SQL Server 2008 (well actually if I remember correctly they were snuck in 2005 SP2…shhhh).  Logon triggers are a special form of trigger that the database administrator can use in order to monitor logon events. 

Logon triggers can be used for the following purposes:

1. To audit and control server sessions (such as tracking logon activity)
2. Limiting the number of sessions allowed for a particular logon
3. Restricting logons to SQL Server (similar to AD snap-in that lets you set the hours that a user may logon)

Here is an example from books online showing how to set up a logon trigger that limits the number of concurrent sessions for a particular logon, logon_test in this case.  This example, in particular, will limit the concurrent sessions to three.  You must create this logon in order to test this code, or change the logon name to match your requirements.

CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS ‘logon_test’
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= ‘logon_test’ AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = ‘logon_test’) > 3
    ROLLBACK;
END;

Here is some sample code for restricting the user based on logon time (between 12Pm and 1pm in this example for all users except the SA\SQLAdministrator):

CREATE TRIGGER Restrict_Logon_Hours

ON ALL SERVER

FOR LOGON

AS

BEGIN

    IF (DATEPART(HOUR, GETDATE()) BETWEEN 12 AND 13) AND

          (SUSER_SNAME() != ‘AD\SQLAdministrator’)

    BEGIN

        ROLLBACK;
    END;

END;

Once you have created logon triggers, you can view them by a simple query to the sys.server_triggers metadata.