Click here to monitor SSC
  • Av rating:
  • Total votes: 62
  • Total comments: 7
Cristian Lefter

Logon Triggers

10 October 2007

Login Triggers were quietly introduced in SP2 to tighten up the security features of SQL Server to comply with the latest industry standards for security. But you can meet a lot of the security requirements even without them!

When I asked a friend from the SQL Server development team "what's the story behind Logon Triggers?" the answer was brief "Common Criteria compliance for SQL Server and nothing more". That is, indeed, the short story but if you work with SQL Server 2005 and need the long story, read on.

A little bit of Common Criteria mumbo jumbo

Today, for most businesses that involve IT, security is a must and not a luxury (as it was just a few years back). But what does security for a computer product really mean? It is not so easy to give an answer that would satisfy everyone. Fortunately for us IT folks, there are several standards that define security.

One of these standards is the Common Criteria (CC) certification, which specifies the security functionalities of a product, as well as the methods to evaluate them. The CC evolved from three other standards: ITSEC (the European standard), CTCPEC (the Canadian standard) and TCSEC (The United States Department of Defense Standard).

What you need to know about the Common Criteria certification is that it is recognized by more than twenty-four nations and that it specifies seven levels of assurance for a computer product, EAL1 to EAL7.

For Windows Server, SQL Server and other software products designed for commercial distribution, an certification level of EAL4 is considered to be adequate.

SQL Server 2005 was evaluated at Common Criteria certification EAL1 for Service Pack 1 and EAL4+ for Service Pack 2. The "+" from EAL4 represents "flaw remediation" that is not part of EAL4. It means that Microsoft will provide SQL Server customers with security patches or corrective actions for any security flaw found in the product.

One of the features introduced by Service Pack 2, in order to achieve EAL4+, was Logon Triggers. They allow SQL Server to comply with the following requirements:

  1. The ability to restrict the maximum number of concurrent sessions that belong to the same user.
  2. The ability to configure a default maximum number of sessions per user.
  3. The ability to deny session establishment based on user identity and/or group identity, time of day, and day of week.

Do I really need Logon Triggers to implement these security functionalities? Is there any work-around? I encountered these questions more than a few times, from curious or service pack resistant people, so I decided to try to find a work-around, knowing that discovering one would mean outsmarting Microsoft developers (a very hard thing to do but not an impossible one).

A Logon Triggers work-around in SQL Server 2005 pre SP2

Have you ever seen the following screen?

It's a screenshot of the Active Directory Users and Computers snap-in that allows you to customize the logon hours for a user (among other things).

Don't search for something similar in SQL Server 2005 pre SP2, because it doesn't exist. However, its provision is a requirement of the EAL4 evaluation, as listed above (the ability to deny session establishment based on time of day, and day of week).

For this requirement I found an easy work-around, using SQL Server Agent jobs. Let's suppose that one of the users in my organization, named Thomas Anderson, should be allowed to connect to the SQL Server box, every day of the week from 9AM to 5PM except Saturday and Sunday.

All I have to do is to disable his login each day at 5PM and enable it each day at 9AM. The following code does just that:

USE msdb;
-- Create a job
EXEC sp_add_job @job_name=N'LogonHours';
-- Add a T-SQL step to the job
EXEC sp_add_jobstep
      @job_name = N'LogonHours',
      @step_name=N'Enable/Disable login',
      @command=N'IF DATEPART(hour,GETDATE())=9
               ALTER LOGIN thomas_anderson ENABLE
               ALTER LOGIN thomas_anderson DISABLE'
-- Associate a schedule to the job
-- to run the job a 5PM
EXEC sp_add_jobschedule
   @job_name = N'LogonHours',
-- Associate a second schedule to the job
-- to run the job at 9AM
EXEC sp_add_jobschedule
   @job_name = N'LogonHours',

Let me explain the code just a little bit. We create a job and associate to with two schedules. One schedule that will execute the job each working day at 9AM and a similar schedule that will execute the job at 5PM. The job itself runs T-SQL code that enables the thomas_anderson login if it's 9AM and disables it otherwise.

So far so good, the work-around works like a charm. What about the other requirements? Can I restrict the number of connections for a user using the same method? No!

A possible scenario for restricting the number of connections is the next one: first the user establishes a connection, then we check the number of already existing connections from this user and if the maximum number of connections that we allow is already attained we kill the new connection.

To do that we need something that will notify us when the user tries to connect. Something close to what we need is the AUDIT_LOGIN SQL Trace event, which fires when a user establishes a successful connection. We can use this event with SQL Profiler or with Event Notifications. The first option, SQL Profiler, is not actually an option, unless we plan to sit watching a screen 24/7, and manually ending connections.

Event Notifications allow the automation of our scenario.

Note: The Event Notifications use the Service Broker infrastructure by sending a message about an event to a Service Broker service.

To use Event Notifications, we need a queue that will hold the events, a service, a route, an event notification and a stored procedure that will do the work. As there's no need to reinvent the wheel we can adapt the code using the base samples from Books Online.

Here's the code:

USE master;
-- Create demo database
-- Create demo login
CREATE LOGIN thomas_anderson
WITH PASSWORD = 'yukon9.0';
-- Enable Service Broker for DemoDB database if it's the case
      SELECT *
      FROM sys.databases
      WHERE [name]=N'DemoDB' AND is_broker_enabled=0
-- We will access from the activated stored procedure a view that is
-- located in a different database
-- the sys.dm_exec_sessions dynamic management view
-- The security context of the stored procedure would not allow us to do so
-- unless we set the the TRUSTWORTHY option to ON.
-- Why and another method you can find here:
-- Create a queue
CREATE QUEUE Logon_Triggers_Queue;
-- Create a service
CREATE SERVICE Logon_Triggers_Service
ON QUEUE Logon_Triggers_Queue([]);
-- Create a route
CREATE ROUTE Logon_Triggers_Route
WITH SERVICE_NAME = N'Logon_Triggers_Service',
-- Create the event notification at the server level for the AUDIT_LOGIN event
CREATE EVENT NOTIFICATION Successfull_Login_Notification
TO SERVICE 'Logon_Triggers_Service', 'current database';

-- Create the stored procedure that will handle the events
-- First set the options required to work with the XML data type
CREATE PROCEDURE usp_Logon_Triggers

-- Use an endless loop to receive messages
WHILE (1 = 1)
      DECLARE @messageBody VARBINARY(MAX);
      DECLARE @messageTypeName NVARCHAR(256);
           WAITFOR (
                  RECEIVE TOP(1)
                    @messageTypeName = message_type_name,
                    @messageBody = message_body
                    FROM Logon_Triggers_Queue
                 ), TIMEOUT 500
      -- If there is no message, exit
      IF @@ROWCOUNT = 0
         BREAK ;
        END ;
      -- If the message type is EventNotification do the actual work
        IF (@messageTypeName =
            DECLARE @data XML;
         DECLARE @LoginName NVARCHAR(128);
            SET @data = CAST(@messageBody AS XML);
         -- Get the SPID and the Login name using the value method
         SET @SPID = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'VARCHAR(5)');
         SET @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(128)');
         -- Check the login name
         IF @LoginName=N'thomas_anderson' AND
            (SELECT COUNT(*) FROM sys.dm_exec_sessions
                  WHERE is_user_process = 1
                  AND original_login_name = 'thomas_anderson') > 1
               -- Kill the current connection if there is already one session established
               -- as thomas_anderson
               EXECUTE ('KILL ' + @SPID);


-- Link the stored procedure to the Logon_Triggers_Queue
ALTER QUEUE Logon_Triggers_Queue
        PROCEDURE_NAME = usp_Logon_Triggers,
        EXECUTE AS SELF) ;

The creation of the queue, service, event notification and route are pretty straightforward but if you need further details on setting up Service Broker, please refer to this article:

The stored procedure is activated when a message is posted to the service. If the message type is event notification, the code gets the login name and the SPID (Session ID) of the new connection from the event data.

The information posted to the Logon_Triggers_Service, about the AUDIT_LOGIN event, has the following structure:


Most of the elements are self explanatory:

  • event_type is obviously AUDIT_LOGIN.
  • post_time contains the time when the event is posted to the service.
  • spid represents the ID of the session for the event.
  • text_data contains a semicolon-delimited list of all set options.
  • binary_data contains the session level settings, including ANSI nulls, ANSI padding, cursor close on commit, null concatenation, and quoted identifiers.
  • database_id is the ID of the default database or the ID of the database used by the USE database statement if any.
  • nt_user_name represents the Windows user name.
  • nt_domain_name represents the Windows domain to which the user belongs.
  • host_name contains the name of the computer on which the client is running.
  • client_process_id is the ID assigned by the host computer to the process where the client application is running.
  • application_name is the name of the client application.
  • login_name is the name of the login used.
  • start_time represents the time that the event started.
  • event_subclass indicates if the connection is pooled or non-pooled with values 1 for non-pooled and 2 for pooled.
  • success element indicates if the authentication succeeded and has the values 1 for success and 0 for failure. For the AUDIT_LOGIN event it will always contain value 1.
  • integer_data represents the network packet size.
  • server_name represents the name of the instance of SQL Server on which the event occurred.
  • database_name is the name of the database
  • login_sid contains the security identification number (SID) of the logged-in user.
  • request_id represents the ID of the request.
  • event_sequence represents the sequence of the event within the request.
  • is_system indicates if the event occurred for a user or a system process. In this case it would always be a user process.

You can see a fragment of the event data on my computer:

TextData>-- network protocol: LPC
set quoted_identifier on

ApplicationName>Microsoft SQL Server Management Studio - Query</ApplicationName>
IsSystem />
SessionLoginName />

We need only the SPID (session ID) of the connection that fired the event and the login name. We obtain them by using value method of the XML data type.

Once we have the SPID and the login name, we can check the number of existing connections for the user (in this case our own Mr. Anderson) and if the maximum number is already attained, we can kill the new connection. The maximum is set to 1 in this case to avoid opening too many query windows for testing the code.

Let's see it at work!

In SQL Server Management Studio, open a query window and login as thomas_anderson. Next, open a new query and login again as thomas_anderson. Don't be fooled by the status message "Connected from SSMS". As soon as you execute something in the new window (like for example SELECT GETDATE();), the connection status will change to Disconnected and you will get an error:

"Msg 233, Level 20, State 0, Line 0

A transport-level error has occurred when sending the request to the server.
(provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

At first look, it seems that we've succeeded in emulating a logon trigger, but let's take a step back. Did we manage to connect? Yes! And that violates the Common Criteria requirement to be able to limit the number of connections for a user. Eventually, our mechanism worked but the key word here is "eventually". That means that Mr. Anderson could hypothetically run a script before being disconnected. And it should be like that. Event Notifications are an asynchronous mechanism.

Disabling our own mechanism

Just for educational purposes I will illustrate how we can make the Event Notifications workaround totally useless.

As you probably know SQL Server 2005 does its own scheduling. Without spending too much time with this: a task (an execution request) is executed by a worker which is mapped to either an OS thread or a logical thread (a fiber).

On my computer, a one proc x86 machine, there are, by default, a maximum of 256 worker threads. I obtained this value from the max_worker_count column of the sys.dm_os_sys_info dynamic management view.

What do you think would happen if SQL Server receives more than 256 requests at the same time? Some of those requests will run before the usp_Logon_Triggers stored procedure. Ergo, if Mr. Anderson sends 256 requests, the Event Notifications-based mechanism won't be able to kill them all.

To run multiple requests at the same time we can use the OSTRESS command line utility, which you can download from:

Have you ever noticed that before you do anything you have to do something else first? This case is no exception to this rule. Before we get to run the OSTRESS tool, we first need to create first a table and allow Mr. Anderson to write to this table, in order to determine how many times he can avoid our Logon Triggers substitute.

So, open SQL Server Management Studio and run the following code:

-- Create demo table
   VAL VARCHAR(128));
-- Create a database user for Mr. Anderson
CREATE USER thomas_anderson FOR LOGIN thomas_anderson;
-- Grant INSERT permission to the user on the new table
GRANT INSERT ON tblDemo TO thomas_anderson;

Then open a command line and navigate to the folder that contains the ostress.exe executable. Next type the following text and hit ENTER:

ostress.exe -l600 -Uthomas_anderson -Pyukon9.0 -Slpc:%COMPUTERNAME% -n256 -Q"INSERT INTO DemoDB..tblDemo SELECT 'I am in!';" -q

The l parameter specifies the timeout, and the others are: the login name (U) the password (P), the server and the protocol used (Slpc), the query text (Q) and the request to discard the output of the query, if any (q). You can read more about the parameters in the help of OSTRESS utility.

After running the above statement, switch back to SQL Server Management Studio and let's see what happened by running:


On my computer the result is 253, and that means that out of 256 statements sent to the server, 253 succeeded. The general network error message is generated when the Logon Triggers surrogate manages to kill a request.

Besides this, if our user has enough rights to send an ALTER QUEUE statement to the server he can disable the whole mechanism.

To conclude, the Event Notifications method didn't make me smarter than Microsoft guys. However, you can use this approach if you need only to audit login information. The asynchronous nature of Event Notifications makes them ideal for auditing.

Before moving to the next section, please run the clean-up code:

-- Drop the event notification
-- Can be done from any database context as it is defined
-- at the server level
DROP EVENT NOTIFICATION Successfull_Login_Notification
-- Drop the service
DROP SERVICE Logon_Triggers_Service;
-- Drop the route
DROP ROUTE Logon_Triggers_Route;
-- Drop the queue
DROP QUEUE Logon_Triggers_Queue;
-- Drop the stored procedure
DROP PROCEDURE usp_Logon_Triggers

Logon Triggers

As we've just seen, an asynchronous method does not fulfill the Common Criteria requirements. So the solution from Microsoft is a new type of trigger, called the logon trigger.

A logon trigger fires when a session is established. At that point the LOGON event is raised. The Logon event corresponds to the AUDIT_LOGIN SQL Trace event used in the previous section.

The life-cycle of a logon trigger is very simple: a user connects to Sql Server, the trigger fires, an implicit transaction is opened and then…then it's up to you! If, for any reason, you want to deny the attempt to log in to Sql Server, just issue a ROLLBACK statement and you're done.

Let's see the previous example written using a logon trigger instead of Event Notifications:

USE master;
CREATE LOGIN security_login WITH PASSWORD = 'yukon9.0';
CREATE TRIGGER connection_limit_trigger
ORIGINAL_LOGIN()= 'thomas_anderson' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'thomas_anderson') > 1

The sys.dm_exec_sessions dynamic management view allows you to see all sessions on a SQL Server instance, provided that you have the VIEW SERVER STATE permission (or you belong to the sysadmin server role). Otherwise you will see only your current session.

To prevent granting unnecessary rights to Mr. Anderson, we create a new login and grant it the VIEW SERVER STATE permission. Then we create the trigger to run under its security context. The rest of the code needs no explanation. However I want to make some important observations:

  1. The ROLLBACK statement rolls back all data modification and will close the connection, but if there are any statements after the ROLLBACK, they will be executed. Any data modification that occurs after the ROLLBACK statement is not rolled back to allow you eventually to log the connection attempt in a table.
  2. Returning results from triggers is not a good practice as the client application may not expect a result set. The capability to return result sets in triggers will be removed from future versions of SQL Server. In logon triggers, returning result sets is prevented. So don't try to return a result set or your trigger will fail.
  3. Any PRINT messages, messages issued using RAISERROR statement or other error messages would be diverted to the SQL Server log.

To test it, open SQL Server Management Studio and then open two query windows as thomas_anderson. The second log on process will fail and an error message will be displayed.

Quite simple isn't it?

Creating logon triggers

To create a logon trigger, you can use the CREATE TRIGGER statement with the following syntax:

CREATE TRIGGER trigger_name
[ WITH <logon_trigger_option> [ ,...n ] ]
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }
<logon_trigger_option> ::=
    [ EXECUTE AS Clause ]

<method_specifier> ::=

The named trigger is registered at the server level and resides in the master database. The code of the trigger could be either T-SQL or CLR code.

The WITH ENCRYPTION clause allows you to encrypt the definition of the trigger.

If you do not specify an execution context for the trigger, using the EXECUTE AS clause, the default behavior is to use the context of the caller of the module.

Once created, you can modify or drop logon triggers using the ALTER TRIGGER or DROP TRIGGER statements, respectively.

Getting information inside logon triggers

To get at information inside a logon trigger you can use the EVENTDATA() function. In this particular case, this will return xml information having the following schema:


The LOGON event schema is somewhat similar to the AUDIT_LOGIN event schema, only simpler:

  • event_type is LOGON.
  • post_time contains the time for session request.
  • The is_pooled element indicates if the connection is using connection pooling (value 1) or not (value 0).

The rest of the elements are as described for the AUDIT_LOGIN event.

Besides the EVENTDATA() function, you can also use system functions such as ORIGINAL_LOGIN() used in the previous example if you need additional information.

CLR Logon Triggers

In most everyday scenarios, you probably would not need CLR code to implement a Logon Trigger. However, to demonstrate that there's nothing too complicated about it, here's an example. We will create a CLR Logon Trigger to audit successful logons in a table:

  1. Open SQL Server Management Studio and in a new query window run the following code to create the audit table:

    USE DemoDB;
    -- Create the audit table
    CREATE TABLE tblAudit(
       [PostTime] DATETIME,
       [ServerName] NVARCHAR(128),
       [LoginName] NVARCHAR(128),
       [LoginType] NVARCHAR(64),
       [SID] VARBINARY(85),
       [ClientHost] NVARCHAR(128),
       [IsPooled] BIT);
    USE master;
    -- Create a login to use for the execution context
    -- of the new trigger
    CREATE LOGIN audit_login WITH PASSWORD = 'yukon9.0';
    -- Create a database user for the new login
    USE DemoDB;
    CREATE USER audit_user FOR LOGIN audit_login;
    -- Grant INSERT permission to the new user
    GRANT INSERT ON tblAudit TO audit_user;

  2. Then open Visual Studio 2005.
  3. On the File menu select New Project.
  4. Expand Database projects and select SQL-CLR as the project type.
  5. In the same window SQL Server Project C# template, enter CLRLogonTrigger in the Name textbox, C:\Projects for location and click OK.
  6. On the Add Database Reference window, click Cancel.
  7. In the Solution Explorer, right click the solution name and select Add - Trigger.
  8. In the Add New Item window - enter for the name DemoTrigger.
  9. Replace the auto-generated code with the following:

using System;
using System.Data;
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Transactions;

public partial class Triggers

    [Microsoft.SqlServer.Server.SqlTrigger(Name = "DemoTrigger", Target = "ALL SERVER", Event = "LOGON")]
    public static void DemoTrigger()
            using (SqlConnection connection = new SqlConnection(@"context connection=true"))
            // Get the trigger context
            SqlTriggerContext triggContext = SqlContext.TriggerContext;
            // Create a new SqlCommand
            SqlCommand command = new SqlCommand();
            // Get event's data
            XmlDocument doc = new XmlDocument();

            // Open the connection.

            // Build the INSERT statement using parameters
            command.Connection = connection;
            command.CommandText = @"INSERT INTO DemoDB..tblAudit(
                                    VALUES (
            // Add parameters
            // PostTime
            SqlParameter parameter = new SqlParameter("@PostTime", SqlDbType.DateTime);
            parameter.Value = doc.DocumentElement.SelectSingleNode("/EVENT_INSTANCE/PostTime").InnerText;
            // SPID
            parameter = new SqlParameter("@SPID", SqlDbType.SmallInt);
            parameter.Value = doc.DocumentElement.SelectSingleNode("/EVENT_INSTANCE/SPID").InnerText;
            // ServerName
            parameter = new SqlParameter("@ServerName", SqlDbType.NVarChar, 128);
            parameter.Value = doc.DocumentElement.SelectSingleNode("/EVENT_INSTANCE/ServerName").InnerText;
            // LoginName
            parameter = new SqlParameter("@LoginName", SqlDbType.NVarChar, 128);
            parameter.Value = doc.DocumentElement.SelectSingleNode("/EVENT_INSTANCE/LoginName").InnerText;
            // LoginType
            parameter = new SqlParameter("@LoginType", SqlDbType.NVarChar, 64);
            parameter.Value = doc.DocumentElement.SelectSingleNode("/EVENT_INSTANCE/LoginType").InnerText;
            // SID
            parameter = new SqlParameter("@SID", SqlDbType.VarBinary, 85);
            parameter.Value = doc.DocumentElement.SelectSingleNode("/EVENT_INSTANCE/SID").InnerText;
            // ClientHost
            parameter = new SqlParameter("@ClientHost", SqlDbType.NVarChar, 128);
            parameter.Value = doc.DocumentElement.SelectSingleNode("/EVENT_INSTANCE/ClientHost").InnerText;
            // IsPooled
            parameter = new SqlParameter("@IsPooled", SqlDbType.SmallInt);
            parameter.Value = doc.DocumentElement.SelectSingleNode("/EVENT_INSTANCE/IsPooled").InnerText;

            // Execute the statement
        catch (Exception ex)
            //If the audit failed prevent the connection
            // Get the current transaction and roll it back.
            Transaction trans = Transaction.Current;

10. Build the project and then close Visual Studio.

11. Back in SQL Server Management Studio run the following script to create the trigger:

USE [master]
-- Import the assembly
-- Create the trigger
 EXTERNAL NAME [LogonTriggers].[Triggers].[DemoTrigger]

12. To test the trigger open a new Query Window and run the following:

FROM DemoDB..tblAudit;

You can see the output on my computer (and it should be similar on yours):

EventID SPID ServerName    LoginName            LoginType
------- ---- ----------    -------------------- ------------------
1       54   MICROTRAINING MICROTRAINING\Cristi Windows (NT) Login

What did we just do? We used the attributes of the CLR module (Name, Target and Event) to specify that the DemoTrigger method should be registered as a trigger and also specify the name, the target and the event that activates the trigger.

The information about the event (LOGON event in this case) is exposed in the EventData property of the SqlTriggerContext class.

In T-SQL, we would use XQuery to parse the XML data but in our .NET code we can use an XmlDocument. The rest of the code uses ADO .NET to insert the data into the table. If anything fails we rollback the current transaction, and consequently the trigger.

After you've tested the CRL trigger, you can clean-up by running the following T-SQL code:

USE master;
-- Drop the trigger
-- Drop the assembly
DROP ASSEMBLY LogonTriggers;

Additionally, you can drop the DemoDB database and the logins created:

USE master;
DROP LOGIN security_login;
DROP LOGIN thomas_anderson;

Special circumstances

Pop quiz: You run the following statement

CREATE TRIGGER DenyAnyConnection

What happens after you hit F5?

A. You've succeeded to lock yourself out of the server.

B. You can still connect.

Most of you will tend to choose A, which is the wrong option and that's because Logon Triggers do no fire if you connect using the DAC (Dedicated Administrator Connection) or if the server is started in the minimal configuration mode.

When I created my first CLR trigger, it crashed for every connection due to a conversion error. I already had a connection open so I ran the DISABLE TRIGGER statement to disable the trigger. Alternatively, I could have used the DAC to save the day.

Therefore, remember that you have those two options to disable a Logon Trigger that prevents any connection.

  1. Use DAC
  2. Stop your server and restart it using the minimal configuration mode

When you can, favor the DAC method instead of restarting your server.

Logon Triggers on SQL Server 2000

While doing the research for this article, I encountered a question on a forum:

"What complicated hacks can be used to implement logon triggers on SQL Server 2000?"

I wasn't aware that such a thing was possible, so I forwarded the question to an MS guy. He answered: "Sure! We have a patch that makes a SQL Server 2000 behave like a 2005 server. There's just one problem: it's expensive. It costs almost as much as a 2005 box!"

I've worked with SQL Server 2005 since 2003 at that time and I didn't know of such a patch. Shame on me! After two days of intense investigation I gave up and I asked the MS guy for a direct link to the patch. He gave me this one:

I felt like a dummy for the second time. It was a link for buying SQL Server 2005. Anyway those two days weren't lost. I learned that developers from Microsoft can have a great sense of humor.

To return to the question: my personal opinion is that you cannot emulate exactly the power of SQL Server 2005 SP2 logon triggers on previous versions of SQL Server.

Instead of a conclusion

The way I see logon triggers is that they are just a framework that allow you to implement lots of custom scenarios.

For example, you can restrict the application used to connect to the server, allow or deny connections from a specific host, stop regular users from connecting during maintenance periods, and so on.

Now I have some observations that you can call "best practices" if you like, but may more accurately be called "common sense":

  • The logon trigger fires for everyone, so make its code as short and efficient as possible. Just imagine that someone opens a node in SQL Server Management Studio - Object Explorer and the trigger fires. Then he wants to see an object definition - the trigger fires again. You get my point.
  • If you have to use transactions inside logon triggers remember that if you issue a ROLLBACK TRANSACTION, the connection is gone.
  • Use error handling inside your triggers. Guess what happens if a transaction-abort error occurs? Not a hard question and the answer is "connection closed".
  • If you implement multiple logon triggers, you can specify which one should be the first or the last by using the sp_settriggerorder system stored procedure. Keep in mind that any ROLLBACK TRANSACTION statement rolls back all data modifications done by the current trigger but also by the previous executed triggers, if any.

Leaving behind Logon Triggers, SQL Server 2008 introduces another type of DDL trigger that is fired by all types of server and database events. This trigger is used to support a new feature called the Declarative Management Framework, which allows you to manage your server using policies. But hey, that's another story!

Cristian Lefter

Author profile:

One of the SQL Server MVPs, Cristian Lefter is a former developer, database administrator and currently CEO of MicroTraining a consulting and training company. In his spare time he is running two user groups ITBoard and Romanian SQL Server User Group.

Search for other articles by Cristian Lefter

Rate this article:   Avg rating: from a total of 62 votes.





Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.

Subject: waw
Posted by: Anonymous (not signed in)
Posted on: Thursday, October 18, 2007 at 3:59 AM
Message: waaaw great job , keep writing..

Subject: Very useful
Posted by: Anonymous (not signed in)
Posted on: Thursday, October 18, 2007 at 8:58 AM
Message: Christan, this is a great intro to the new logon trigger feature. Thanks for taking the time.

Subject: very good!
Posted by: Piotr Rodak (not signed in)
Posted on: Thursday, October 18, 2007 at 10:51 AM
Message: At first, I wondered, why would you like to emulate logon triggers, but in the end I found your story very educational, thanks!

Subject: SQL 2000
Posted by: Ralf (not signed in)
Posted on: Thursday, October 18, 2007 at 12:37 PM
Message: In Sql 2000 you can modify sp_'s from MS to accomplish strong pw-checking, log-on deny, and user session blocks.
In 2005 you cannot use this way but in 2000 it was/is working, but - of course - you're loosing support from the vendor ;-)

Subject: systems procedures to accomplish log-on deny in SQL 2000?
Posted by: RemusRusanu (not signed in)
Posted on: Saturday, October 20, 2007 at 1:40 AM
Message: Ralf, can you share some details about what system procedures can one modify in SQL 2000 to accomplish the feats you meantion?

Subject: Thanks!
Posted by: Anonymous (not signed in)
Posted on: Tuesday, December 18, 2007 at 6:04 PM
Message: Definitely locked myself out of the server with a logon trigger, thanks for the DAC tip!

Subject: SQL 2005 login trigger
Posted by: Anonymous (not signed in)
Posted on: Monday, August 4, 2008 at 4:38 PM
Message: Can Try - Catch be used within a logon trigger T-sql ?

Simple-Talk Database Delivery

Patterns & Practices Library

Visit our patterns and practices library to learn more about database lifecycle management.

Find out how to automate the process of building, testing and deploying your database changes to reduce risk and make rapid releases possible.

Get started

Phil Factor
Routine SQL DML Testing for the Unenthusiastic Tester

There are more exciting things in life than unit testing SQL Statements, checking the results, timings, and... Read more...

 View the blog

Top Rated

Jodie Beay and the Production Database Drift
 You make an example database, like NorthWind or WidgetDev in order to test out your deployment system... Read more...

SQL Server Data Tools (SSDT) and Database References
 SQL Server Data Tools (SSDT) provides, via the DacPac, interesting support for verifying not only... Read more...

Writing Build vNext tasks for Visual Studio Online
 Hosted TFS, now called Visual Studio Online (VSO), has a new way of writing build processes called... Read more...

Microsoft and Database Lifecycle Management (DLM): The DacPac
 The Data-Tier Application Package (DacPac), together with the Data-Tier Application Framework (DacFx),... Read more...

A Start with Automating Database Configuration Management
 For a number of reasons, it pays to have the up-to-date source of all the databases and servers that... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

Temporary Tables in SQL Server
 Temporary tables are used by every DB developer, but they're not likely to be too adventurous with... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.