Auditing

06 Jun 2022 37503 views 0 minutes to read Contributors

SQL Server Audit

Auditing an instance of the SQL Server Database Engine or an individual database involves tracking and logging events that occur on the Database Engine. SQL Server audit lets you create server audits, which can contain server audit specifications for server level events, and database audit specifications for database level events. Audited events can be written to the event logs or to audit files.

More info here: https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine

Triggers

Create a DML, DDL, or logon trigger. A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server. DML triggers run when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event fires, whether table rows are affected or not. 

More info here: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql

Temporal tables

SQL Server 2016 introduced support for temporal tables (also known as system-versioned temporal tables) as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. Temporal is a database feature that was introduced in ANSI SQL 2011.

More info here: https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables

Change Data Capture

Change data capture records insert, update, and delete activity that is applied to a SQL Server table. This makes the details of the changes available in an easily consumed relational format. Column information and the metadata that is required to apply the changes to a target environment is captured for the modified rows and stored in change tables that mirror the column structure of the tracked source tables. Table-valued functions are provided to allow systematic access to the change data by consumers.

More info here: https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server

Extended Events

SQL Server The Extended Events architecture enables users to collect as much or as little data as is necessary to troubleshoot or identify any change. Extended Events is configurable, and it scales very well.

More info here: https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events

There are more techniques avaialble.