In case you decide to go the Event Notification route, here's a generic sample of how to implement it. All this does is maintain a table of logins and the last time they were used. Though not done in this example, you can also configure all your servers to log to a central repository.
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Audit')
DROP DATABASE [Audit]
GO
USE [master]
GO
CREATE DATABASE [Audit]
GO
ALTER DATABASE [Audit]
SET ENABLE_BROKER;
GO
USE [Audit];
GO
CREATE TABLE AuditLoginsLog (
LoginNamevarchar(128),
LastLogindatetime)
GO
--CREATE SERVICE PROCEDURE
CREATE PROCEDURE [dbo].[auditLogins_usp]
AS
BEGIN
SET NOCOUNT ON;
SET ARITHABORT ON;
DECLARE @message XML,
@messageName NVARCHAR(256),
@dialogue UNIQUEIDENTIFIER ;
BEGIN TRY
--Continuous loop
WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION;
--Retrieve the next message from the queue
SET @dialogue = NULL;
WAITFOR (
GET CONVERSATION GROUP @dialogue FROM dbo.AuditLoginsQueue
), TIMEOUT 2000;
IF @dialogue IS NULL
BEGIN
ROLLBACK;
BREAK;
END
;RECEIVE TOP(1)
@messageName=message_type_name,
@message=message_body,
@dialogue = conversation_handle
FROM dbo.AuditLoginsQueue
WHERE conversation_group_id = @dialogue;
IF EXISTS (SELECT LoginName FROM AuditLoginsLog where LoginName = @message.value('(/EVENT_INSTANCE/LoginName)[1]', 'VARCHAR(128)'))
UPDATE AuditLoginsLog SET LastLogin = @message.value('(/EVENT_INSTANCE/PostTime)[1]', 'DATETIME')
WHERE LoginName = @message.value('(/EVENT_INSTANCE/LoginName)[1]', 'VARCHAR(128)')
ELSE
INSERT INTO AuditLoginsLog VALUES (
@message.value('(/EVENT_INSTANCE/LoginName)[1]', 'VARCHAR(128)'),
@message.value('(/EVENT_INSTANCE/PostTime)[1]', 'DATETIME') )
COMMIT;
END
END TRY
BEGIN CATCH
DECLARE @errorNumber INT,@errorMessage NVARCHAR(MAX),@errorState INT,@errorSeverity INT,@errorLine INT,@errorProcedure NVARCHAR(128)
SET @errorNumber = error_number();
SET @errorMessage = error_message();
SET @errorState = error_state();
SET @errorSeverity = error_severity();
SET @errorLine = error_line();
SET @errorProcedure = error_procedure();
if not(xact_state() = 0)
ROLLBACK;
RAISERROR('%s:%d %s (%d)',@errorSeverity,@errorState,@errorProcedure,@errorLine,@errorMessage,@errorNumber) WITH log;
END CATCH
END
GO
--CREATE QUEUE
CREATE QUEUE AuditLoginsQueue
WITH STATUS = ON
,ACTIVATION (
PROCEDURE_NAME = dbo.auditLogins_usp ,
MAX_QUEUE_READERS = 2, EXECUTE AS SELF)
GO
--CREATE SERVICE
CREATE SERVICE AuditLoginsService
ON QUEUE [AuditLoginsQueue]
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO
--CREATE ROUTE
CREATE ROUTE AuditLoginsRoute
WITH SERVICE_NAME = 'AuditLoginsService',
ADDRESS = 'Local'
GO
--CREATE EVENT
CREATE EVENT NOTIFICATION AuditLogins_event
ON SERVER
WITH FAN_IN
FOR AUDIT_LOGIN
TO SERVICE 'AuditLoginsService', 'current database'
GO