DDL Events.; 4 minutes to read; Contributors. In this article. APPLIES TO: SQL Server (starting with 2008) Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse The following tables list the DDL events that can be used to fire a DDL trigger or event notification.
I am dealing with some sensitive Accounting tables and I would like to audit any
SELECT
statement executed on the table or any views associated with them.I did not find any DDL Events on BOL (Books Online) that had anything to do with
SELECT
statement.And DML triggers are for INSERT
, UPDATE
and DELETE
only.Is it possible to log who accesses table and views through
Sung M. KimSung M. KimSELECT
statement?![Sql server tool to generate log table and triggers for sale Sql server tool to generate log table and triggers for sale](/uploads/1/2/4/9/124932439/566481013.png)
18.5k3333 gold badges112112 silver badges167167 bronze badges
6 Answers
You have 3 options:
- allow access via stored procedures if you want to log (and remove table rights)
- hide the table behind a view if you want to restrict and keep 'direct' access
- run a permanent trace
I'd go for options 1 or 2 because they are part of your application and self contained.
Although, this does sound a bit late to start logging: access to the table should have been restricted up front.
Also, any solution fails if end users do not correct directly (eg via web server or service account). Unless you use stored procs to send in the end user name...
View example:
gbngbn353k6060 gold badges499499 silver badges587587 bronze badges
Yes, it is possible by creating an Event Notification on the AUDIT_DATABASE_OBJECT_ACCESS_EVENT event. The cost of doing something like this would be overwhelming though.
It is much better to use the audit infrastructure, or using custom access wrapper as gbn recommends.
Remus RusanuRemus Rusanu250k3232 gold badges365365 silver badges494494 bronze badges
I just added some code for you. The code creates a server audit, a database audit for select activities and finally the sys.fn_get_audit_file is used to retrieve the information from the file. You have to do that individually for each table. If you want a more automated query, you can use other tools like Apex SQL Audit or other third party tool of your preference.
![Sql server trigger on insert Sql server trigger on insert](http://www.datasparc.com/wp-content/uploads/2015/12/sql-server-table-ddl.png)
Daniel CalbimonteDaniel Calbimonte
SQL Server 2008 Auditing may be able to capture it. Other than that, Profiler/Tracing is the only thing in SQL Server that can do it.
RBarryYoungRBarryYoung44.4k1212 gold badges8282 silver badges119119 bronze badges
Edit : Viewing and Analyzing Traces with SQL Server Profiler
anishMarokeyanishMarokey10.1k22 gold badges2727 silver badges4343 bronze badges
Gregory HartGregory Hart