Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using Service Broker to capture event details for auditing Expand / Collapse
Author
Message
Posted Thursday, April 7, 2011 10:00 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 2:27 PM
Points: 94, Visits: 1,461
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 »

Add to briefcase

Permissions Expand / Collapse