September 17, 2012 at 11:11 am
Collen,
Thanks for your info. Can you provide me the steps to use event notifications and Service Broker to audit DDL and DML events in Standard Edition.
September 17, 2012 at 11:39 am
The example below will create a basic DML audit. The only downside of tracking DML using Event Notifications is that the AUDIT_SCHEMA_OBJECT_ACCESS_EVENT group can only be used at the server level. So be sure to specify the database(s) you want to audit in your procedure, and exclude the audit table, or you'll end up with that never-ending loop mentioned earlier in reference to DDL triggers. Here I'm auditing the AdventureWorks database. See this link for a full list of the fields returned by this event.
Obviously an audit of this scope can potentially track a lot of activity, so be careful and test fully before deploying anything in a production environment.
USE [master]
GO
CREATE DATABASE [Audit]
GO
ALTER DATABASE [Audit]
SET ENABLE_BROKER;
GO
USE [Audit];
GO
CREATE TABLE [dbo].[AuditObjectAccess] (
LoggingID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
SQLInstance VARCHAR(100),
DatabaseName VARCHAR(100),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
DatabaseUser VARCHAR(100),
ClientHostName VARCHAR(100),
NTUserName VARCHAR(100),
NTDomainName VARCHAR(100),
SchemaName VARCHAR(100),
ObjectName VARCHAR(100),
ObjectType VARCHAR(100),
Success INT,
FullSQL varchar(max),
FullLog XML
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[auditObjectAccess_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.auditAccessQueue
), 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.auditAccessQueue
WHERE conversation_group_id = @dialogue;
IFISNULL((@message.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(100)')), 'NULL') = 'AdventureWorks'
BEGIN
INSERT INTO AuditObjectAccess (
SQLInstance,
DatabaseName,
EventTime,
EventType,
LoginName,
DatabaseUser,
ClientHostName,
NTUserName,
NTDomainName,
SchemaName,
ObjectName,
ObjectType,
Success,
FullSQL ,
FullLog )
VALUES
(
@message.value('(/EVENT_INSTANCE/ServerName)[1]', 'VARCHAR(100)'),
ISNULL(@message.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(100)'), 'SERVER'),
@message.value('(/EVENT_INSTANCE/PostTime)[1]', 'DATETIME'),
@message.value('(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(100)'),
@message.value('(/EVENT_INSTANCE/LoginName)[1]', 'VARCHAR(100)'),
@message.value('(/EVENT_INSTANCE/DBUserName)[1]', 'VARCHAR(100)'),
@message.value('(/EVENT_INSTANCE/HostName)[1]', 'VARCHAR(100)'),
@message.value('(/EVENT_INSTANCE/NTUserName)[1]', 'VARCHAR(100)'),
@message.value('(/EVENT_INSTANCE/NTDomainName)[1]', 'VARCHAR(100)'),
@message.value('(/EVENT_INSTANCE/SchemaName)[1]', 'VARCHAR(100)'),
@message.value('(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(50)'),
@message.value('(/EVENT_INSTANCE/ObjectType)[1]', 'VARCHAR(50)'),
@message.value('(/EVENT_INSTANCE/Success)[1]', 'INTEGER'),
@message.value('(/EVENT_INSTANCE/TextData)[1]', 'VARCHAR(max)'),
@message);
END
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 auditAccessQueue
WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = audit.dbo.auditObjectAccess_usp ,
MAX_QUEUE_READERS = 2, EXECUTE AS SELF)
GO
--CREATE SERVICE
CREATE SERVICE auditAccessService
ON QUEUE [auditAccessQueue]
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO
--CREATE EVENT NOTIFICATION
CREATE EVENT NOTIFICATION ObjectAccess_event
ON SERVER WITH FAN_IN
FOR AUDIT_SCHEMA_OBJECT_ACCESS_EVENT
TO SERVICE 'auditAccessService', '1B2E3D93-A1A3-4003-A754-A9E308D74253' --Broker ID for Audit database
GO
You can use a similar setup for auditing DDL events. Just be sure to check BOL for the fields returned by that event. DDL auditing can be created at the database or server level.
--CREATE DDL EVENT NOTIFICATION
USE AdventureWorks
GO
CREATE EVENT NOTIFICATION ObjectDDL_event
ON DATABASE WITH FAN_IN
FOR DDL_DATABASE_LEVEL_EVENTS
TO SERVICE 'auditDDLService', '1B2E3D93-A1A3-4003-A754-A9E308D74253' --Broker ID for Audit database
GO
Hope this helps.
September 17, 2012 at 12:46 pm
Thanks collen for your immediate info. However, I tried your program on my database. I tried to do some DML operations, but unforutnately I don't see any audit listed on table "AuditObjectAccess". Is there any other place we need to monitor for auditing. I am new to concept of service broker..can you guide me please. Thanks
September 17, 2012 at 12:53 pm
Did you modify the stored procedure to filter on your database name?
IFISNULL((@message.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(100)')), 'NULL') = 'AdventureWorks'
Also, did you modify the Event Notification definition with the Service Broker GUID of your Audit database?
CREATE EVENT NOTIFICATION ObjectAccess_event
ON SERVER WITH FAN_IN
FOR AUDIT_SCHEMA_OBJECT_ACCESS_EVENT
TO SERVICE 'auditAccessService', '1B2E3D93-A1A3-4003-A754-A9E308D74253'
GO
Is there anything in the auditAccessQueue?
September 17, 2012 at 1:37 pm
WHERE CAN I FIND "Service Broker GUID of your Audit database?
"
September 17, 2012 at 1:46 pm
You can find it in the service_broker_guid of sys.databases.
September 17, 2012 at 2:53 pm
oh yeah..forgotten about this...
Anyways now everything working..so I did some DML operations and when I queried on select * from [AuditObjectAccess]..I am not getting pretty precise information of what change went ..like what insert/update...don't we get any information of what exactly happened...like we get in sqltrace...
September 17, 2012 at 3:09 pm
I guess I'm not entirely clear as to exactly what events and data you want to capture. Are you looking for what rows were affected by the DML?
July 20, 2016 at 10:18 am
Great post. When I set this up, I noticed the DML audit created a feedback loop because the insert into audit table is another DML event. I could filter this out in the procedure, but I'm wondering if there is a better way?
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply