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

AS

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.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s