Post reply

Dynamic Trace

  • August 23, 2005 at 8:34 am

    #152521

    I need to run security audit trace continuously to monitor user’s login activity.  Is there a trace flag I can set in the startup parameters that will enable security audit trace?  Once I collect the data, I can write home grown procedures to run reports on it.  I do not know of any alternates other than the security audit trace.  If there is one, please feel free to share.

     

    Thanks

     

  • August 24, 2005 at 8:34 am

    #584608

    I don't know of a trace flag, but I once wrote a stored procedure that created a trace to log all CREATEs, ALTERs, etc., then set it to run on startup.  You can explore the trace procedures in BOL (they're different for 7 vs 2000), or better yet create it in Profiler and then script it out: File-> Script Trace->.  You'll have to change where you want the trace written to, a file vs a table.

     

    Dylan Peters
    SQL Server DBA

  • August 24, 2005 at 12:37 pm

    #584741

    Thanks...seems like I will have to do the same thing you did.  Does anyone know if SQL Server 2005 has something to audit logins?

  • August 29, 2005 at 1:51 pm

    #585967

    I was able to use sp_trace_create and sp_trace_setevent to collect the user login and logout information.  The trace runs fine when windows authentication is used.  However, when I tried to use SQL Server login, it does not collect the workstation (ClientHostName) information.  Any ideas?

     

    Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply