Dynamic Trace

Question

Dynamic Trace

Eric M.

Ten Centuries

Points: 1098
More actions
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

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

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

Dylan Peters SSC Eights! Points: 916 More actions · Answer 1

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

Eric M. Ten Centuries Points: 1098 More actions · Answer 2

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?

Eric M. Ten Centuries Points: 1098 More actions · Answer 3

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