Thursday 9 July 2009

SQL Login Auditing

Source:
http://www.sqlservercentral.com/articles/SQLServerCentral/sqlserver2005logontriggers/2366/

Sql Simplified:


CREATE DATABASE LoginAudit
GO

--Enable service broker
ALTER DATABASE LoginAudit SET ENABLE_BROKER
GO
USE LoginAudit


CREATE TABLE LoginAttempt (
ID int IDENTITY Primary Key,
LoginTime DATETIME Not Null,
Status VARCHAR(20) Not Null,
LoginName VARCHAR(100) NULL,
HostName VARCHAR(100) NULL,
NTUserName VARCHAR(100) NULL,
NTDomainName VARCHAR(100) NULL,
Success tinyInt NULL,
FullLog XML NULL
)
GO

--The queue
CREATE QUEUE QueLoginAttempt
GO

--The service
CREATE SERVICE LoginMonitorService
ON QUEUE QueLoginAttempt
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO

CREATE EVENT NOTIFICATION Login_Event_Notification
ON SERVER
FOR AUDIT_LOGIN, AUDIT_LOGOUT, AUDIT_LOGIN_FAILED
TO SERVICE 'LoginMonitorService', 'current database'
GO

SELECT * FROM QueLoginAttempt



SELECT * FROM sys.server_event_notifications



Alter PROCEDURE p_LogLoginAttempts
AS
Begin

DECLARE @message_body XML,
@message_type_name NVARCHAR(256),
@dialog UNIQUEIDENTIFIER ;

--Endless loop
WHILE (1 = 1)
BEGIN
-- Receive the next available message

WAITFOR (
RECEIVE TOP(1)
@message_type_name=message_type_name,
@message_body=message_body,
@dialog = conversation_handle
FROM QueLoginAttempt
), TIMEOUT 2000

--Rollback and exit if no messages were found
IF (@@ROWCOUNT = 0)
BEGIN
BREAK ;
END ;

--End conversation of end dialog message

IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
--PRINT 'End Dialog received for dialog # ' + cast(@dialog as nvarchar(40)) ;
END CONVERSATION @dialog ;
END ;
ELSE
BEGIN


INSERT INTO LoginAttempt (
LoginTime,
Status,
LoginName,
HostName,
NTUserName,
NTDomainName,
Success,
FullLog)
VALUES
(
CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS VARCHAR(64)) AS DATETIME),
CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/HostName/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/NTUserName/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/NTDomainName/text()') AS VARCHAR(100)),
CAST(CAST(@message_body.query('/EVENT_INSTANCE/Success/text()') AS VARCHAR(64)) AS INTEGER),
@message_body)
END

END
End


ALTER QUEUE QueLoginAttempt
WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = p_LogLoginAttempts ,
MAX_QUEUE_READERS = 1, EXECUTE AS SELF )

SELECT * FROM LoginAttempt



select cast(message_body as xml) from QueLoginAttempt

No comments:

Post a Comment