SQL Server Performance Forum – Threads Archive

Auditing

Hello, I would like to set up an audit on one of my table to see who updates it, when its updated, and the statment issued to update the table? Since its just one table I’m trying to avoid C2 audit. So, I’m thinking about setting up a trigger on the table for update. I know I can use the USER and GETDATE() function to get the id and the date/time of the update. How can I get the sql statement issued? Thanks

I don’t think the trigger has access to the T-SQL sent to the server. But since your trigger is ON UPDATE and you can check columns (and the values therein) that have changed using the UPDATED() function, it is possible to reconstruct the UPDATE statement that was sent to the server. Nathan H.Omukwenyi

Without using third party tool you can run server side trace to capture underline SQL statements. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Probably the easiest way for this is to run a server-side trace. See if this helps:
http://msdn.microsoft.com/library/d…us/dnsqlmag01/html/TrackingDownEventClues.asp —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

One of the problems with running server-side traces is ensuring the the trace continues running. If this is more than a temporary low-priority solution I would suggest third party software.

True and ensure enough disk space is configured to store those trace files, or input any mechanism to transfer those traces to a staging server for investigation purpose. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>