Audit db table

Find out which databases and applications need to be audited Corresponding application, database, SQL Server instance, etc

Determine if triggers, the OUTPUT clause, stored procedure logic, application logic, Service Broker or some combination of these approaches will be responsible for writing the data to the audit tables

Audit tables are used by native or 3rd party auditing tools that capture data changes that have occurred on a database, usually including :,the information on who made the change, which objects were affected by it, when it was made as well as the information on the SQL login, application and host used to make the change

The Database-Level Audit Specification object belongs to an audit

You can create one database audit specification per SQL Server database per audit

For more information, see SQL Server audit (Database Engine)

Database audit specifications are non-securable objects that reside in a given database

Auditing an instance of SQL Server or a SQL Server database involves tracking and logging events that occur on the system

The SQL Server Audit object collects a single instance of server-level or database-level actions and groups of actions to monitor

The audit is at the SQL Server instance level

Find out which databases and applications need to be audited Corresponding application, database, SQL Server instance, etc

Determine if triggers, the OUTPUT clause, stored procedure logic, application logic, Service Broker or some combination of these approaches will be responsible for writing the data to the audit tables

The design from the base table to the audit table are very similar, but the audit table has some additional columns to support managing the historical data (i

AuditID to serve as the identity, ModifiedBy to serve as a means to capture who made the change, etc

Let's use a sample AdventureWorks table ( [HumanResources]

This is where audit tables come in

Audit tables can be used to track the before and after value of the changed data

Usually they also save extra information like the person who did the change and the time at which the change was done

Hence these audit tables deter empowered users from performing unauthorized activities