How to audit all SQL Queries in a database?

How can you audit all queries within a database?

Hi Joshua,

there is builtin functionality for this. Youcan either implement your own logic at your frontend for doing so, using an additional layer between the database on the data components or only use stored procedure to retrieve the data with logging the actions inside the stored procedure.

If you want to use a third party logreader like lumigent you can explore the transaction logs for statements.

-Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Joshua,

Look for SQL Trace or Profiler in Books Online.

|||Audit sounds to me (could be to my non-native language english) as you want to audit or trace SQL queries for a long time. profiler is very costy and should only be started as a debugging tool, not for running it all the time. I just wanted to make sure that you keep that in mind. Otherwise, using the Profiler for a short amount of time to find out problems and queries fired against the database is a usual and daily business for developers and administrators.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

If your interest this in the control of changes in the registries of a table, you can use triggers to before know the state the data of the registry and despues of being updated.

Respuesta en idioma original:

Si tu interes esta en el control de cambios en los registros de una tabla, puedes utilizar disparadores para conocer el estado de los datos del registro antes y despues de ser actualizado.

|||Profiler is very costly, but you can set up a job to run a trace, just for certain types of events, like Batch Complete and RPC Complete, which will capture all of the queries run. If you script this in a job, then direct the results to a trace file on the hard drive of your server, the cost is minimal. You can then use fn_get_traceinfo to review the trace files, and even load them into a database table for analysis.|||Thanks Allen, this was helpful. I'll give it a try.