Menu
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 SELECT statement?
217
A trigger is a database object that, unless disabled, automatically executes a specified set of SQL statements, called the trigger action, when a specified trigger event occurs. The trigger event that initiates the trigger action can be an INSERT, DELETE, UPDATE, or a SELECT statement.
The DDL triggers in SQL Server are fired in response to a variety of data definition language (DDL) events such as Create, Alter, Drop, Grant, Denay, and Revoke (Table, Function, Index, Stored Procedure, etc…). That means DDL triggers in SQL Server are working on a database.
DDL triggers fire only after the DDL statements that trigger them are run. DDL triggers cannot be used as INSTEAD OF triggers. DDL triggers do not fire in response to events that affect local or global temporary tables and stored procedures. DDL triggers do not create the special inserted and deleted tables.
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.
134
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
