May 282008
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.
Hey Jim,
I stumbled across your post above and it does what I have been looking to do with some additions on my part to do some cleanup in some of our db tables. It works perfectly on my workstation (XP serviidce pack 3) but when I put it on Windows Server 2003 64bit with SQL Server 2008 64 bit the script completes sucessfully and all is well except no notifications are getting put in the audit table. Is there aything you might know of off hand that would be keeping this from working on Windows Server vs Windows XP?