SQL Server - DML Commands auditing against user database objects.

Asked By prathap chowdary on 22-Jun-12 08:27 AM
Earn up to 30 extra points for answering this tough question.
Hi,

This is Prathap...... Actually client requirement is
 '' They need auditing DML commands against user database objects."

here i am facing problem ..because our all production servers running with SQL Server 2008 standard edition.
so , in standard editions Auditing feature not supported.

And i have tried through SQL server profilers.......in profiler counters...there is Objects folder is there....in that there options is there : objects:created, objects:Altered, objects:Deleted.
so profilers giving spid's for the above profilers options.......

but how to find out sql query's for that particular spid's..........
in the above scenarion i have tried with below query :

select

* from sys.dm_exec_sessions as s

inner join sys.dm_exec_requests as req on s.session_id=req.session_id

cross apply sys.dm_exec_sql_text(req.sql_handle)

where



s.session_id=61



In the above query also have a problem......because if i am running 2 querys with this session id : 61 .....i am not getting all sql text for that spid.

Please any one help me........is there any another scenario's..........

Note : We are using SQL Server 2008 standard edition.

Regards,
Prathap.
Robbe Morris replied to prathap chowdary on 22-Jun-12 02:09 PM
I think you are after this sort of technique:

http://schottsql.blogspot.com/2010/02/ddl-schema-change-auditing-on-sql.html