Example 1
A relatively simple but very relevant usage of SQL Server Audit is to monitor the activity of privileged users accessing a sensitive table. This can easily be accomplished with the SQL Server Audit feature in SQL Server 2008 by setting up the proper database
audit specification. For this example, assume that the sensitive table is in the hr_db database and is called hr.salary and we want to detect when the dbo (and consequently sysadmin) tries to query the table. In this example, the audit information will be
written to a file on a remote share called
\\AuditServer\Audit, on which the SQL Server service instance has permissions to write but not read or modify.
First we need to create the Audit and the Database Audit Specification objects.
Transact-SQL Copy Code
USE master
CREATE SERVER AUDIT audit1 TO FILE (FILEPATH='\\AuditServer\Audit')
USE hr_db
CREATE DATABASE AUDIT SPECIFICATION hr_dbspec FOR SERVER AUDIT audit1
ADD(SELECT,UPDATE,INSERT,DELETE ON hr.salary by dbo)
Because the objects are created disabled by default, they need to both be enabled.
Transact-SQL Copy Code
USE master
ALTER SERVER AUDIT audit1 WITH (STATE=ON)
USE hr_db
ALTER DATABASE AUDIT SPECIFICATION hr_dbspec WITH (STATE=ON)
At this point, the audit is turned on and all queries against the hr_db table by the dbo or the sysadmin are recorded, as are any actions that enable or disable the audit.
To confirm that both the audit and the audit specification are enabled, the system views can be queried.
Transact-SQL Copy Code
SELECT is_state_enabled FROM sys.server_file_audits
SELECT is_state_enabled FROM sys.database_audit_specifications
Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
