Logon Triggers aka How To Lock That Annoying User Out During Lunch

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.

Advertisements

About SQLGator

Florida Gator and Microsoft Data Platform MVP, Star Wars fanatic and is there anything else...oh yeah PS4! I am a geek and SQL Server DBA who dabbles with VMWare, there are other technologies greater than these? Not so fast my friend! I also love to travel to new and exotic places.

Posted on January 27, 2012, in Database Administration Tasks and tagged , , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: