Auditing User Log Out Events

A post came up a few days ago on the MSDN SQL Forums, wanting to know how to audit server log offs.  He mentioned that there is a Log In trigger, but couldn’t find a log out trigger.  After a bit of thought, and a quick discussion with Rob Farley, we figured that Service Broker should be able to process the AUDIT_LOGOUT events and capture the results to a table.

Here’s the code:

USE master;
GO
CREATE DATABASE AuditLogOuts;
GO

-- Enable Service Broker for the new database
ALTER DATABASE AuditLogOuts SET ENABLE_BROKER;
GO

USE AuditLogOuts;
GO
-- Create a queue
CREATE QUEUE Queue_Audit_LogOut;
GO
-- Create a service
CREATE SERVICE Service_Audit_LogOut
ON QUEUE Queue_Audit_LogOut([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO
-- Create a route
CREATE ROUTE Logon_Triggers_Route
WITH SERVICE_NAME = N'Service_Audit_LogOut',
ADDRESS = N'LOCAL';
GO
-- Create the event notification at the server level for the AUDIT_LOGIN event
CREATE EVENT NOTIFICATION Notification_Audit_LogOut ON SERVER
FOR AUDIT_LOGOUT TO SERVICE 'Service_Audit_LogOut', 'current database';
GO

CREATE TABLE AuditLogOuts(
      DateAudited datetime not null default getdate()
    , PostTime datetime
    , SPID int
    , DatabaseID int
    , NTUserName nvarchar(128)
    , NTDomainName nvarchar(128)
    , HostName nvarchar(128)
    , ApplicationName nvarchar(128)
    , LoginName nvarchar(128)
    , Duration bigint
    , SourceData XML
);
GO

-- Create the stored procedure that will handle the events
-- First set the options required to work with the XML data type
CREATE PROCEDURE usp_Process_Audit_LogOut AS
BEGIN
    SET NOCOUNT ON;
    -- Use an endless loop to receive messages
    WHILE (1 = 1)
    BEGIN
        DECLARE @messageBody VARBINARY(MAX);
        DECLARE @messageTypeName NVARCHAR(256);
        WAITFOR (
            RECEIVE TOP(1) @messageTypeName = message_type_name, @messageBody = message_body
            FROM Queue_Audit_LogOut
        ), TIMEOUT 500
        -- If there is no message, exit
        IF @@ROWCOUNT = 0
        BEGIN
            BREAK;
        END;

        -- If the message type is EventNotification do the actual work 
        IF (@messageTypeName = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification')
        BEGIN

            DECLARE
                  @xmldata xml
                , @PostTime datetime
                , @SPID int
                , @DatabaseID int
                , @NTUserName nvarchar(128)
                , @NTDomainName nvarchar(128)
                , @HostName nvarchar(128)
                , @ApplicationName nvarchar(128)
                , @LoginName nvarchar(128)
                , @Duration bigint

            SET @xmldata = CAST(@messageBody AS XML);

            SET @PostTime = @xmldata.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime');
            SET @SPID = @xmldata.value('(/EVENT_INSTANCE/SPID)[1]', 'int');
            SET @DatabaseID = @xmldata.value('(/EVENT_INSTANCE/DatabaseID)[1]', 'int');
            SET @NTUserName = @xmldata.value('(/EVENT_INSTANCE/NTUserName)[1]', 'nvarchar(128)');
            SET @NTDomainName = @xmldata.value('(/EVENT_INSTANCE/NTDomainName)[1]', 'nvarchar(128)');
            SET @HostName = @xmldata.value('(/EVENT_INSTANCE/HostName)[1]', 'nvarchar(128)');
            SET @ApplicationName = @xmldata.value('(/EVENT_INSTANCE/ApplicationName)[1]', 'nvarchar(128)');
            SET @LoginName = @xmldata.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)');
            SET @Duration = @xmldata.value('(/EVENT_INSTANCE/Duration)[1]', 'bigint');

            INSERT INTO AuditLogOuts(SourceData, PostTime, SPID, DatabaseID, NTUserName,
                NTDomainName, HostName, ApplicationName , LoginName, Duration)
            VALUES (@xmldata, @PostTime, @SPID, @DatabaseID, @NTUserName,
                @NTDomainName, @HostName, @ApplicationName , @LoginName, @Duration)

            -- Process your cleanup, based on your username, or SPID, or whatever
        END;
    END;
END;
GO

-- Link the stored procedure to the Queue_Audit_LogOut 
ALTER QUEUE Queue_Audit_LogOut WITH STATUS=ON,
   ACTIVATION (STATUS=ON, PROCEDURE_NAME = usp_Process_Audit_LogOut, MAX_QUEUE_READERS = 1, EXECUTE AS SELF);
GO

-- Open and close some connections, then have a look in the table:
SELECT * FROM AuditLogOuts;

-- Rollback - drop the database and remove the event notification.
/*
USE Master
GO
DROP DATABASE AuditLogOuts
GO
DROP EVENT NOTIFICATION Notification_Audit_LogOut ON SERVER
*/

Special thanks to Cristian Lefter’s article for the original framework for using Service Broker.