If you want to audit what's happening on your SQL Server, then look no further. SQL Server 2008 has a built in function for that, so you can log all Queries like
Deletes performed on a certain table easily, before you will be needing third party tools to do this or use SQL Trace which slows down your database.
Now to achieve this, it's as easy as 6 steps.
- Create an Audit, you can easily do that by going the security section and Audits.
It will just ask you for an Audit Name and where to save and what to save as File, Security Log or Application Log.
- Now you have an Audit, you need to create an Audit Specification by Database you want to audit. Here is a script on how to do it:
CREATE DATABASE AUDIT SPECIFICATION TestAuditSpecification
FOR SERVER AUDIT TestAudit
ADD (SELECT ON dbo.SampleTable BY dbo),
ADD (INSERT ON dbo.SampleTable BY dbo),
ADD (UPDATE ON dbo.SampleTable BY dbo),
ADD (DELETE ON dbo.SampleTable BY dbo),
ADD (EXECUTE ON dbo.SampleTable BY dbo)
That sample will audit all
Execute and Permission Changes on
SampleTable performed by a DBO.
- Verify if the script created the object by going to the database’s security section under database audit specifications.
Double click it if you want to view or alter any specification you want.
Different Audit Action types can be chosen on the menu and add what you need. You can view the definitions here.
- Enable both Audit and Audit Specification Object.
- Perform any
Delete query to test if it's working.
- Check if the audit is successful by going to your chosen log, for my example, I used Application Logs.
It's that easy!!!