Using Service Broker to capture event details for auditing

• Ryan D.

  SSCommitted

  Points: 1595

  More actions

  April 7, 2011 at 10:00 am

  #235301

  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