Database Auditing ~ Who Did What?

Real life scenario ~ I’ve seen companies that I’ve come into not have their production environment on lock down and every one who is anyone has access to the environment.

I like the idea of having a table with the following columns:

AuditID – Unique Identifier
AuditDate – Date the event occurred
LoginName – Who did the event
ObjectName – What object was affected
Command – The command that was executed
EventType – What kind of event occurred
ServerName – The server the Event occurred on
DatabaseName – Which Database the Event occurred on

Once the table is created you can create a database trigger that will dump data into your table so you can track who is doing what in the database at any given time. I’ve used this practice at previous job sites and was happy to see this being utilized at my current employer.

The trigger will look something similar to this:

CREATE TRIGGER [tr_Trigger Name]

ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

BEGIN

SET NOCOUNT ON

DECLARE @EventData XML
SET @EventData = EVENTDATA()

BEGIN TRY

INSERT INTO [DATABASE NAME].dbo.[TABLE NAME]
(
AuditDate,
LoginName,
ObjectName,
EventType,
ServerName,
DatabaseName,
Command
)
SELECT
GETDATE(),
t.c.value(‘(LoginName)[1]’,’VARCHAR(255)’),
t.c.value(‘(ObjectName)[1]’,’VARCHAR(255)’),
t.c.value(‘(EventType)[1]’,’VARCHAR(100)’),
t.c.value(‘(ServerName)[1]’,’VARCHAR(255)’),
t.c.value(‘(DatabaseName)[1]’,’VARCHAR(255)’),
t.c.value(‘(TSQLCommand/CommandText)[1]’,’NVARCHAR(MAX)’)
FROM
@EventData.nodes(‘/EVENT_INSTANCE’) t(c)

–Note you can add a where clause and exclude “Stats” if you so desire”

END TRY
BEGIN CATCH
EXECUTE [DATABASE NAME].dbo.[SP TO CAPTURE ERRORS]
END CATCH

END

I’ve found this technique useful in running reports after deployments on what was deployed; going back for future reference on what was done, capturing issues among team members of not knowing what they were doing in order to correct it, and just for overall satisfaction of knowing what is occurring on the database itself.

If you’re not sure what is occurring on your databases then maybe this is something that you can try to help build a base to capture events that are occurring from drops, creates, to grants, etc.