By: K. Brian Kelley | Comments (6) | Related: > Auditing and Compliance
Problem
Management has asked that I audit when data is read from a table in the database. I can't use triggers, because they don't fire on SELECT statements. Is there a method other than running a SQL Server Profiler or server side trace to audit when SELECTs are issued against tables?
Solution
Prior to SQL Server 2008, the only option is the trace or the use of some 3rd party product. With SQL Server 2008 Enterprise Edition, the Audit object and a database-level Audit Specification can monitor when a SELECT statement is executed against a particular table.
The first thing to do is to create an Audit object using SQL Server Management Studio (SSMS). Audit objects are located under Security and then Audits. If you right-click on Audits you can choose New Audit from the pop-up menu. The Create Audit dialog window is shown in Figure 1.
Figure 1
You'll need to specify the audit name as well as the destination. There are 3 possible destinations: the Application event log, the Security event log, or a file folder. If you want to write to the Security event log, there are some additional steps you might need to perform. These are documented in Books Online.
For this example we'll choose a file path and specify a folder of C:\Temp\SelectAudit. This folder must already exist. Click OK to create the Audit object.
Once you've created an Audit object, you'll need to create an Audit Specification in the appropriate database. Let's setup to monitor the HumanResources.Employee table in the AdventureWorks2008 database. Database audit specifications are located under Security and Database Audit Specifications within the database. By right-clicking on Database Audit Specifications, we get a pop-up menu where we can select the option to create a new Database Audit Specification. A dialog window like what is shown in Figure 2 will appear.
Figure 2
Once again you'll need to enter a name. You'll also need to point it at a particular Audit object. In this case, point it at Audit_Select_HumanResources_Employee. Then you'll need to configure an Audit Action Type. For this Audit Specification, choose SELECT for the Audit Action Type, OBJECT for the Object Class, and HumanResources.Employee for the Object Name. If you want to audit for anyone who may issue a SELECT against this table, choose the public role as the Principal to audit. Click OK to create the new Database Audit Specification.
Once the Audit and the Database Audit Specification are created, you'll need to enable them. This is done by right-clicking on the Audit or the Database Audit Specification and selecting the "Enable..." option. You should get a dialog window which tells you the Audit or Database Audit Specification was enabled successfully. Once both are enabled, auditing on the HumanResources.Employee table has been configured.
If you execute a SELECT statement against HumanResources.Employee, the Audit object should show that event. Right-click on the Audit object and select View Audit Logs. You should see an entry like in Figure 3, which shows the details, to include who executed the query and the exact query that was run.
Figure 3
On a closing note, the Audit object is only available with SQL Server 2008 Enterprise Edition. Prior versions of SQL Server do not have this feature. Versions of SQL Server 2008 less than Enterprise Edition also do not have this feature. However, if you have Enterprise Edition installed, the Audit object can give you a lot of flexibility to monitor certain server or database-level events without a full trace.
Next Steps
- If auditing SELECT statements is one of your needs, look into using this new feature. Although the cost difference of using Standard versus Enterprise is quite hefty it may be a less expensive option than other third party tools.
- Stay tuned for future auditing and SQL Server related tips
About the author
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
