SQLTeam.com | Weblogs | Forums

Sql auditing solution


#1

Being forced to make ad-hoc DML changes on prod db's. Client has no concept of versioning, managed deployments, ..etc and just wants to roll in changes.

Insisting on some kind of auditing. CDC, CDT, Triggers do not work as changes can occur anywhere, anytime. Was thinking using service account which is audited to record all DML change in a table somewhere.

What method would you recommend?


#2

I don't fully understand this claim:

CDC, CDT, Triggers do not work as changes can occur anywhere, anytime.

CDC and triggers should pick up any change at any time. In fact, it should be more reliable than a specific account, which could be bypassed to make untracked changes using a different business account.


#3

CDC, CDT is table level and needs to be enabled in advance.

My requirement could be required on multiple instances, dozens of databases and 1000s of tables, so logistically cannot enable CDC, CDT on the entire prod estate.

Which is why would like to track DML changes by login if possible.


#4

This will track object changes DDL changes

IF Object_Id(N'dbo.DDLAudit') IS NOT NULL DROP TABLE dbo.DDLAudit
go
CREATE TABLE dbo.DDLAudit
( PostTime datetime2(7)
, SUserName sysname
, DatabaseName varchar(256)
, EventType nvarchar(100)
, ObjectName varchar(256)
, TSQLCommand nvarchar(2000)
, LoginName varchar(256)
);
GO
DROP TRIGGER AuditProcChanges ON DATABASE;
GO
CREATE  TRIGGER AuditProcChanges  
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
    CREATE_VIEW, ALTER_VIEW, DROP_VIEW
AS

DECLARE @ed XML
SET @ed = EVENTDATA()

INSERT INTO dbo.DDLAudit (PostTime, SUserName, DatabaseName, EventType, ObjectName, TSQLCommand, LoginName) 
VALUES
(
	SysDateTime(),
        SUser_Name(),
	@ed.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
	@ed.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
	@ed.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
	@ed.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
	@ed.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
) 
GO

If you're using SQL 2016 the temporal table feature would allow auditing DML changes.


#5

Thanks Guys.

CDC, CDT, Temporal Tables, Triggers, Audit Traces, C2 Auditing, Service Side Traces, XE all have there place depending on use case.

This use case; prod ad-hoc manual DML statements can be required on any 1000s tables, 100s DBs at any time. Cannot setup the above options arbitrarily across entire estate, and activating most of them introduces risk.

Maybe a 3rd party tool for this use case

https://www.idera.com/productssolutions/sqlserver/sqlcompliancemanager/productdetails


#6

how about this method. Any possibility of walking away from this job, seriously. sounds like this is just the tip of the iceberg of wacky requirements they will be presenting you.


#7

If you're not comfortable managing the trace(s) yourself, then a 3rd party app could be very helpful. But, to capture what you want, will require a trace (probably best) or triggers on all tables that you're concerned about.


#8

In response to yosiasz. Your right about the walking away option. Unfortunately when consulting clients throws all kind of ridiculous scenario's your way and you just have to figure something out - even if miles away from best practice.

Server side traces are no problem. Create a template, filter on SPID or login (service account) and capture DML activity for the duration of the live update and store off in a table. Thanks will pursue this approach.

Cheers!