Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2006
    Posts
    31

    Unanswered: Audit Delete Statements

    Hi

    I was curious whether it's possible to audit DELETE statements in the MS SQL database. I created a procedure (below), but I didn't find any event associated with DELETE statements.

    Any help will be greatly appreciated!

    Thanks,
    Alla

    CREATE proc sp_Turn_Audit_On
    as
    /************************************************** **/
    /* Created by: SQL Profiler */
    /* Date: 11/15/2006 05:16:40 PM */
    /************************************************** **/


    -- Create a Queue
    declare @rc int
    declare @TraceID int
    declare @maxfilesize bigint
    declare @StatusMsg varchar
    declare @ServerTraceFile varchar
    set @ServerTraceFile = 'E:\Program Files\Microsoft SQL Server\MSSQL\Trace\Audit_Info'
    set @maxfilesize = 1024

    -- Client side File and Table cannot be scripted

    -- Set the events
    declare @on bit
    set @on = 1

    exec @rc = sp_trace_create @TraceID OUTPUT, 0, N'\\hostname\dbauditlog\my_dir', @maxfilesize, NULL
    print @TraceID

    if (@rc != 0) goto error
    exec sp_trace_setevent @TraceID, 14, 1, @on
    exec sp_trace_setevent @TraceID, 14, 6, @on
    exec sp_trace_setevent @TraceID, 14, 9, @on
    exec sp_trace_setevent @TraceID, 14, 10, @on
    exec sp_trace_setevent @TraceID, 14, 11, @on
    exec sp_trace_setevent @TraceID, 14, 12, @on
    exec sp_trace_setevent @TraceID, 14, 13, @on
    exec sp_trace_setevent @TraceID, 14, 14, @on
    exec sp_trace_setevent @TraceID, 14, 16, @on
    exec sp_trace_setevent @TraceID, 14, 17, @on
    exec sp_trace_setevent @TraceID, 14, 18, @on
    -- Set the Filters
    declare @intfilter int
    declare @bigintfilter bigint

    exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'


    -- Set the trace status to start
    exec sp_trace_setstatus @TraceID, 1
    --SELECT @StatusMsg = 'sp_trace_setstatus' + ' Error - ' + @TraceID
    -- display trace id for future references
    select TraceID=@TraceID

    goto noCursor

    error:
    select ErrorCode=@rc

    noCursor:
    return


    GO
    exec sp_procoption N'sp_Turn_Audit_On', N'startup', N'true'
    GO

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Take a look at tirggers
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2006
    Posts
    31
    I see...

    I just noticed that there is an event to audit TSQL via SQL Profiler. I spooled the script to a SQL file. however, is there any way of filtering it that it would capture DELETE statements only?

    Thanks,
    Alla

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Spool SQL Profiler output to a table so you can query it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •