Get SQL Statement that caused the change?

I'd like to audit the changes to a table and I'd like to keep track of
the sql statements used to change the table. I know how to get the
system user and the app name, but is it possible to recover the sql as
well? Thanks for any help.
-JohnSee if this helps:
http://vyaskn.tripod.com/tracking_s...by_triggers.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"John Baima" <john@.nospam.com> wrote in message
news:vmdde11i919mouii6jgug74r7n7ms3f17f@.
4ax.com...
> I'd like to audit the changes to a table and I'd like to keep track of
> the sql statements used to change the table. I know how to get the
> system user and the app name, but is it possible to recover the sql as
> well? Thanks for any help.
> -John|||Maybe this will help?
CREATE TABLE #foo
(
EventType SYSNAME,
Parameters SYSNAME,
EventInfo NTEXT
)
DECLARE @.sql NVARCHAR(4000)
SET @.sql = N'DBCC INPUTBUFFER('+RTRIM(@.@.SPID)+') WITH TABLERESULTS'
INSERT #foo EXEC(@.sql)
SELECT EventInfo FROM #foo
DROP TABLE #foo|||Thanks, that's perfect. -John
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote:

>Maybe this will help?
>CREATE TABLE #foo
>(
> EventType SYSNAME,
> Parameters SYSNAME,
> EventInfo NTEXT
> )
>DECLARE @.sql NVARCHAR(4000)
>SET @.sql = N'DBCC INPUTBUFFER('+RTRIM(@.@.SPID)+') WITH TABLERESULTS'
>INSERT #foo EXEC(@.sql)
>SELECT EventInfo FROM #foo
>DROP TABLE #foo
>|||Thanks, that is very useful site in general. -John
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote:

>See if this helps:
>http://vyaskn.tripod.com/tracking_s...by_triggers.htm|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote:

>Maybe this will help?
>CREATE TABLE #foo
>(
> EventType SYSNAME,
> Parameters SYSNAME,
> EventInfo NTEXT
> )
>DECLARE @.sql NVARCHAR(4000)
>SET @.sql = N'DBCC INPUTBUFFER('+RTRIM(@.@.SPID)+') WITH TABLERESULTS'
>INSERT #foo EXEC(@.sql)
>SELECT EventInfo FROM #foo
>DROP TABLE #foo
Thanks, that was great. When I do this, for the EventInfo I get:
sp_executesql;1
for the EventType I get:
RPC Event
and the parameter is 0. Is there anyway to get more information and
know what was passed to sp_executesql, etc? Thanks again.
-John|||If you're using sp_executesql, things might get a little complicated. Also,
DBCC INPUTBUFFER is limited in that it will not contain all the previous
command(s), especially if you are doing non-T-SQL things like changing SET
options, master..xp_cmdshell, etc. Can you provide a full repro (e.g. the
table structure, the statement that caused the change, and what you expect
as output)?
"John Baima" <john@.nospam.com> wrote in message
news:2bshe1h29uqjaf4fpknskifa5hjkqb4qbn@.
4ax.com...
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote:
>
> Thanks, that was great. When I do this, for the EventInfo I get:
> sp_executesql;1
> for the EventType I get:
> RPC Event
> and the parameter is 0. Is there anyway to get more information and
> know what was passed to sp_executesql, etc? Thanks again.
> -John
>
>
>|||Well, that's the rub. We have someone altering the database and we
would like to know who is doing it. It is not being done by any of the
production programs. The command deletes a single row in a reference
table.
-John
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote:

>If you're using sp_executesql, things might get a little complicated. Also
,
>DBCC INPUTBUFFER is limited in that it will not contain all the previous
>command(s), especially if you are doing non-T-SQL things like changing SET
>options, master..xp_cmdshell, etc. Can you provide a full repro (e.g. the
>table structure, the statement that caused the change, and what you expect
>as output)?
>
>
>"John Baima" <john@.nospam.com> wrote in message
> news:2bshe1h29uqjaf4fpknskifa5hjkqb4qbn@.
4ax.com...
>|||Sounds like a job for profiler, not a trigger or DBCC...
"John Baima" <john@.nospam.com> wrote in message
news:n07ie194j6bib94b9k4vm3sei4qltv5g9v@.
4ax.com...
> Well, that's the rub. We have someone altering the database and we
> would like to know who is doing it. It is not being done by any of the
> production programs. The command deletes a single row in a reference
> table.
> -John
>