For an instructor lead, in-depth look at learning SQL click below.
SQL Server Audit is a potent feature that can help you track and log activities happening in the database. It is particularly useful for maintaining the security and integrity of your data and adhering to compliance obligations.
Understanding SQL Server Audit
The SQL Server Audit feature is designed to create server audit objects in the master database. Using these audit objects, you can capture events that occur at both the database and server levels. Furthermore, you can target specific actions and users for surveillance.
SQL Server Audit Components
SQL Server Audit comprises three main components:
- Server Audit Specification: This defines the server-level events we want to capture. It’s tied to an audit object and scoped to the entire server.
- Database Audit Specification: Similar to the Server Audit Specification, except it is pertinent to individual databases.
- Audit: This includes the destinations where the captured event data will be stored.
Creating and Setting up SQL Server Audit
Let’s start by creating an audit object at the server scope.
|
1 2 3 4 5 6 7 |
CREATE SERVER AUDIT WebsiteAudit TO FILE ( FILEPATH = 'D:\AUDIT\' ); |
This command creates a new Server Audit called “WebsiteAudit” and saves the audit logs onto the drive specified.
Now, let’s set up a Server Audit Specification. The following example is for tracking successful login attempts to the SQL server:
|
1 2 3 4 5 |
CREATE SERVER AUDIT SPECIFICATION WebsiteAuditSpec FOR SERVER AUDIT WebsiteAudit ADD (SUCCESSFUL_LOGIN_GROUP); |
Next we turn these features on by using the “ALTER SERVER” commands.
|
1 2 3 4 |
ALTER SERVER AUDIT WebsiteAudit WITH (STATE = ON); ALTER SERVER AUDIT SPECIFICATION WebsiteAuditSpec WITH (STATE = ON); |
Setting up Database Audit Specification
For database level specification, we can use similar commands:
|
1 2 3 4 5 |
CREATE DATABASE AUDIT SPECIFICATION DatabaseAuditSpec FOR SERVER AUDIT WebsiteAudit ADD (SELECT, INSERT, UPDATE, DELETE ON DATABASE::YourDatabaseName BY PUBLIC); |
This SQL code sets up an audit specification on database level events such as SELECT, INSERT, UPDATE, and DELETE operations, tracing any such activities performed by the public user.
Viewing the Audit Logs
To access the audit logs, you can use the Windows Event Viewer or read the log file data directly. Here’s an example SQL statement to read the data:
|
1 2 3 |
SELECT * FROM sys.fn_get_audit_file ('D:\AUDIT\WebsiteAudit_*.sqlaudit',default,default); |
Conclusion
SQL Server Audit presents a robust mechanism for overseeing database and server events. Properly implemented, it can help maintain and increase security, integrity and assist in the compliance of your data handling practices.