Anyway to create a SQL Server DDL trigger for "SELECT" statements?

Question

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?

Answer 1

Edit : Viewing and Analyzing Traces with SQL Server Profiler

Answer 2

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.

Answer 3

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:

CREATE VIEW dbo.MyTableMask
AS
SELECT *
FROM
    MyTable
    CROSS JOIN
    (SELECT 1 FROM SecurityList WHERE name = SUSER_SNAME())
--WHERE could use NOT EXISTS too with table
GO

Answer 4

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.