Using Service Broker to capture event details for auditing

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Programming » Service Broker » Using Service Broker to capture event details...

Using Service Broker to capture event details for auditing

Rate Topic

Display Mode

Topic Options

Author

Message

Ryan D.

Posted Thursday, April 07, 2011 10:00 AM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 6:10 AM
Points: 92, Visits: 1,271

Hi I'm using SQL 2005
I'm currently using service broker to capture Event Notifications
For the event ALTER_LOGIN, I'm having issues capturing the command being generated. I'm only able to capture the event_type and object name.

I been playing around with different parameters but unable to make it work. Can anyone confirm if ALTER_LOGIN event generates any TSQL command for data capture?

USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_audit_event]
AS
SET NOCOUNT ON
DECLARE @count INT
DECLARE @msgtypeid INT
DECLARE @msg VARBINARY(MAX)
DECLARE @servername varchar(100)
DECLARE @eventtype varchar(100)
DECLARE @loginname varchar(100)
DECLARE @dbname varchar(100)
DECLARE @cmd varchar(500)
DECLARE @posttime smalldatetime
DECLARE @xmlmsg xml

set @count = (select count(*) from DBANotifyQueue)

WHILE @count > 0
BEGIN
WAITFOR(
RECEIVE top(1)
@msgtypeid = message_type_id,
@msg = message_body
FROM DBANotifyQueue
)

set @xmlmsg = @msg
select (convert(nvarchar(max),@msg))

set @servername = (SELECT @xmlmsg.value('/EVENT_INSTANCE[1]/ServerName[1]','varchar(100)') )
set @eventtype = (SELECT @xmlmsg.value('/EVENT_INSTANCE[1]/EventType[1]','varchar(100)') )
set @loginname = (SELECT @xmlmsg.value('/EVENT_INSTANCE[1]/LoginName[1]','varchar(100)') )
set @dbname =(SELECT @xmlmsg.value('/EVENT_INSTANCE[1]/DatabaseName[1]','varchar(100)'))
IF @eventtype = 'DROP_ROLE_MEMBER'
set @cmd = 'sp_droprolemember ' + (SELECT @xmlmsg.value('/EVENT_INSTANCE[1]/RoleName[1]','varchar(500)')) + ', ' + (SELECT @xmlmsg.value('/EVENT_INSTANCE[1]/ObjectName[1]','varchar(500)'))
ELSE
IF @eventtype = 'ALTER_LOGIN'
set @cmd = (SELECT @xmlmsg.value('(/EVENT_INSTANCE/EventType)[1]','varchar(500)')) + ', ' + (SELECT @xmlmsg.value('/EVENT_INSTANCE[1]/ObjectName[1]','varchar(500)'))
--set @cmd = (SELECT @xmlmsg.value('(/EVENT_INSTANCE/EventType)[1]','varchar(500)')) + ', ' + (SELECT @xmlmsg.value('(/EVENT_INSTANCE/TSQLCommand)[1]','varchar(max)'))
-- set @cmd = (SELECT @xmlmsg.value('/EVENT_INSTANCE[1]/TSQLCommand[1]/CommandText[1]','varchar(max)'))
else
set @cmd = (SELECT @xmlmsg.value('/EVENT_INSTANCE[1]/TSQLCommand[1]/CommandText[1]','varchar(500)'))

set @posttime = (SELECT @xmlmsg.value('/EVENT_INSTANCE[1]/PostTime[1]','smalldatetime'))

INSERT INTO msdb.dbo.auditevent (servername, eventtype,loginname,dbname,command,posttime)
values(@servername,@eventtype,@loginname,@dbname,@cmd,@posttime)

set @count = @count - 1
END

Post #1090026

« Prev Topic | Next Topic »

Permissions