Some time ago, one of my customers asked me about a simple audit system. The system must collect all "user" changes in the database. The need appears because a lot of users (like developers, DevOps, technical support, analysts, etc.) have access to the DB. Due to an internal problem, he cannot change or take permissions, but he wanted to be able to track the changes.
In this article, I want to talk about the simplest way to track changes in SQL Server. The idea for this came due to an incident that happened to one of my customers - one of the tables disappeared. Yep, like that, the entire table was missing, and no one knew where it had gone.
The SQL Server database management system did not have a built-in tracking system. It is having everything needed for that, but we need to develop something for ourselves.
* Before we dive into this - don't worry, all examples are already on our Madeira Toolbox on GitHub.
Let's create our changes audit in SQL Server:
Create a target place for the data:
One simple audit table will be enough, and we can place it wherever we want.
Don't forget about safety! Create an additional security layer and keep it locked away from prying eyes and mischievous fingers. Set the audit table on a dedicated database or schema. Don't mix it with regular data and permissions.
We're building a vault for the data's change history. Don't let our audit data become a target. Ensure that only authorized users can access it and no one can edit it.
Example:
USE [YourDBname]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [YourSchemaName].[DDLEventsAudit]
(
-- Your columns list
)
ON [PRIMARY]
GO Create a collecting tool:
Each time when something occurs on the server, the engine accumulates an event about it. Those events are kind of reports about an "incident", and we only need to choose which one to catch and what action applies.
Each event can fire a DDL trigger or send a notification. Both kinds can include the T-SQL statements they cover. So, as you understand, our simplest collecting tool will be a DDL trigger.
The trigger can be created at the instance or database level in SQL Server or Azure SQL Managed Instance. In the case of the Azure SQL database, we can use the database-level option only - "ON DATABASE".
For our needs, we will use only a DDL event, and "FOR DDL_DATABASE_LEVEL_EVENTS" will be enough. If you want more, you can use a whole group of events or a specific one.
In the body of the trigger write a logic to capture relevant information (e.g., event type, object name, user, timestamp, deleted or updated data) and insert it into the chosen place.
Example:
USE [YourDBname]
GO
CREATE TRIGGER [DDLEvents_SampleTrigger]
ON DATABASE / ALL SERVER -- Adjust scope if needed
FOR DDL_DATABASE_LEVEL_EVENTS / DDL_SERVER_LEVEL_EVENTS -- Adjust as it needed
AS
BEGIN
-- Perform additional logging actions here, such as:
-- Writing to audit table
END;
GO Create an action tool:
Well, we have audit data. Nice! But it can be "great"!
Let's take the baton from our DDL catcher and sprint it into action.
A simple AFTER trigger can easily transform captured events into action. More than that, it can notify everyone who needs to know about unwanted or suspected changes.
An AFTER trigger fires only after the triggering SQL statement has run successfully. So, each time a new row with a specific event is inserted into our audit table, an email is sent to the DBA. Just choose a wanted (or unwanted) event and specify the notification recipient.
In the body of this trigger write a logic to identify a relevant event and an additional part for alert sending.
Example:
USE [YourDBname]
GO
CREATE TRIGGER [YourSchemaName].[DDLEvents_NotifyTrigger]
ON [YourDBname].[YourSchemaName].[DDLEventsAudit]
AFTER INSERT
AS
BEGIN
-- Perform additional logging actions here, such as:
-- Sending notifications
-- Custom logic based on your needs
END;
GO
Summary:
In this article, I've demonstrated a free, simple, and secure way to audit changes in SQL Server using DDL triggers. Using a few simple steps, we can create a basic audit system.
In some cases, such audit logging can help you to restore deleted or damaged data. That without restoring any backups, simply by taking it from the audit table.
Remember, this article and the provided scripts are just suggestions. Feel free to adapt them to your unique needs and content!
I hope this helps :)
Additional resources:
Simplest changes audit scripts examples: https://github.com/MadeiraData/MadeiraToolbox/tree/e5236898621b9cc7524d3ad564eba6053c28ee52/Monitoring%20Solutions/Simplest%20changes%20audit
CREATE TRIGGER (Transact-SQL) - Microsoft Learn: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql
DDL Events Groups: https://docs.microsoft.com/en-us/sql/relational-databases/triggers/ddl-event-groups