For an instructor lead, in-depth look at learning SQL click below.
For database administrators, it is crucial to monitor who based on their role accesses the database, which data has been accessed and at what time. This is where SQL Server Database Auditing comes into play. This feature helps administrators track and log access to the database, helping to ensure security and maintain database performance.
Setup of SQL Server Audit
The first step in database auditing is setting up an Audit object. An Audit object defines what action to take when an event occurs that needs to be audited. For instance, the audit can be written to Windows Security logs, Windows Application logs or to a flat file. Here’s a quick example of creating an audit object:
|
1 2 3 4 5 6 |
CREATE SERVER AUDIT Audit_Track_Access TO APPLICATION_LOG WITH (QUEUE_DELAY = 5000, ON_FAILURE = CONTINUE); GO |
SQL Server Audit Trigger
With the audit set up, you can now define which actions to audit. This can include changes to schema, database manipulation language (DML) or data definition language (DDL) with triggers. A database trigger is a stored procedure that automatically executes in response to certain events on a particular table or view in a database.
For instance, if you want to track any INSERT, UPDATE, DELETE operations on a table, you could use a trigger like this:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TRIGGER Track_Access ON Employee_Table AFTER INSERT, DELETE, UPDATE AS BEGIN INSERT INTO Audit_Log (UserName, ActionDate, Action) VALUES (USER, GETDATE(), 'Action Performed'); END; GO |
Reading the Audit Logs
With SQL Server Auditing, you can view the logs directly from Management Studio or use T-SQL to read from the log file. Below is a sample T-SQL script to read an audit log:
|
1 2 3 4 |
SELECT * FROM fn_get_audit_file('C:\Audit_Logs\*', default, default); |
The above query will retrieve all the audit entries from the specified file path.
Conclusion
Auditing is a powerful feature in SQL Server that allows tracking actions performed on the database, ensuring the security and quality of your data. Having a proper audit process in place can save countless hours of problem-solving and provide peace of mind knowing that you have the necessary oversight on your SQL Server data.