SQL Server Performance Forum – Threads Archive

Audit access to specific table

How do I log all access to a specific table with confidential information? The security on the table is relatively tight but I want to know who hits and when just in case. I tried using the profiler tool but it seems to be recording everything, not just access to the table I am concerned with tracking. Thanks,
Jeremy

---

I’d suggest if you can use stored procedures for all access to this procedure, and then you can have whatever auditing you like. Triggers allow you to audit insert/update/delete. SQL Profiler allows you to filter down to any sql where the text is like ‘%tablename%’ If none of these are possible then I’d suggest looking at something like SQLAudit which reads the database log file… Cheers
Twan

---

If you have set up appropriate permissions for this table so that only authorized users have access (and sa, of course), nobody will see the data. If you cannot trust your sysadmin you have more severe problems than audit access to a table. Frank
http://www.insidesql.de
http://www.familienzirkus.de

---

If possible can take help from third party tools like ENTEGRA from LUMIGENT to audit the database events. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

I played around with the server side traces a bit and think that will do what I need. One strange thing is that the events do not get saved into the .trc file until I close the trace. Is there a way to have the trace write to the file automatically? Frank, this has nothing to do with trusting my SAs and everything to do with auditing. In some cases, if the data are confidential enough, it is worthwhile to prove that no unauthorized access has occurred. Thanks,
Jeremy

---

THen you may need to get third party referred above for complete information.. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

]]>