locked
Enabling DDL,DML , security auditing in SQL SERVER 2000 RRS feed

  • Question

  • please help to enable   DDL,DML , security auditing in SQL SERVER 2000

    Sunday, May 22, 2011 8:58 AM

Answers

  • You can set up a stored procedure (turn on profiler) on server startup

    CREATE PROCEDURE sp_blackbox_trace

    AS

    DECLARE @traceid INT;

     

    EXEC sp_trace_create @traceid OUTPUT, @options = 8;

     

    --SELECT @traceid AS [Trace Id];

    EXEC sp_trace_setstatus @traceid = 2, @status =1;

     

    GO

     

    sp_procoption  @ProcName =  'sp_blackbox_trace' 

            ,  @OptionName =  'startup' 

            ,  @OptionValue =  'ON' 

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Stephanie Lv Tuesday, May 24, 2011 7:00 AM
    • Marked as answer by Stephanie Lv Monday, May 30, 2011 11:10 AM
    Monday, May 23, 2011 8:56 AM

All replies

  • Please explain what  you are trying to achieve? Are  you talking about triggers?

    create trigger tru_MyTable on MyTable after update

    as

     

    if @@ROWCOUNT = 0

        return

     

    insert MyAuditTable

    select

        i.ID

    ,   d.MyColumn

    ,   i.MyColumn

    from

        inserted    i

    join

        deleted    d    on    d.ID    = o.Id

    go


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, May 22, 2011 10:29 AM
  • i would like log all the traces to a log file  instead of  any trigger based

    Sunday, May 22, 2011 10:47 AM
  • Explain what did you mean by 'traces'? Profiler? Is that possible to backup log file?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, May 22, 2011 10:49 AM
  • i wanted to write all the logs to .trc file , so i can  read the trace file

     

     

    please refer the link for further  informations 

    http://technet.microsoft.com/en-in/library/dd277388(en-us).aspx

    Sunday, May 22, 2011 11:38 AM
  • So what is the problem. Turn on SQL Server Profiler , capture events and read them 

    SELECT * FROM ::fn_trace_gettable('c:\my_trace.trc', default)
    GO
    


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, May 23, 2011 6:17 AM
  • thanks uri ,

    i have a question , you mean i need to turn on profiler  always , what if  irestart the server ?

    Monday, May 23, 2011 6:44 AM
  • You can set up a stored procedure (turn on profiler) on server startup

    CREATE PROCEDURE sp_blackbox_trace

    AS

    DECLARE @traceid INT;

     

    EXEC sp_trace_create @traceid OUTPUT, @options = 8;

     

    --SELECT @traceid AS [Trace Id];

    EXEC sp_trace_setstatus @traceid = 2, @status =1;

     

    GO

     

    sp_procoption  @ProcName =  'sp_blackbox_trace' 

            ,  @OptionName =  'startup' 

            ,  @OptionValue =  'ON' 

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Stephanie Lv Tuesday, May 24, 2011 7:00 AM
    • Marked as answer by Stephanie Lv Monday, May 30, 2011 11:10 AM
    Monday, May 23, 2011 8:56 AM
  • Any progress?
    Thursday, May 26, 2011 5:52 AM