Re: Auditing

From: Sue Hoegemeier (Sue_H@nomail.please)
Date: 10/01/02 

From: Sue Hoegemeier <Sue_H@nomail.please>
Date: Mon, 30 Sep 2002 20:06:40 -0600

If you use the feature in Enterprise Manager for Audit
level, you can audit logins but it won't audit logoffs per
your request. So to include this level, you need to use
Profiler or a server trace.
Take a look at the sp_trace_xxx store procedures. You can
use sp_trace_setstatus to start a trace. When you execute
sp_trace_create, there is an output parameter for @traceid.
You can then use this traceid to pass to sp_trace_setstatus.
You can create your trace in Profiler - after you create the
trace, go to File, Script Trace which will generate the
trace file. You can trace the events: Audit Login, Audit
Logout.
The following walks you through the above explanation of
creating the trace that's used in the article example:
INF: How to Programmatically Load Trace Files into Tables
(Q270599)
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q270599
Additionally, the following might help as a reference
INF: Stored Procedure to Create a SQL Server 2000 Blackbox
Trace (Q281671)
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q281671
And this shows how to create your own trace from a text file
but I think it's easier to use Profiler:
INF: How to Create a SQL Server 2000 Trace (Q283790)
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q283790

You can set your own stored procedure to execute on startup
with sp_procoption.

-Sue
 
On Mon, 30 Sep 2002 09:56:25 -0700, "Leon Parker"
<lparker@lortobco.com> wrote:

>I'm trying to create auditing to track user logon/logoff.
>How do I use system stored procedures or Profiler to
>create the trace and have it to start automatically
>whenever SQL starts?

Relevant Pages

  • Re: how to determine which user changed permission?
    ... If you enable the C2 Auditing please monitor your hard drive very closely. ... Since C2 Audit will audit all actions in backend and wil cause ... the trace file to grow heavily. ... > Thx Mike ...
    (microsoft.public.sqlserver.security)
  • Re: Setting up Trace in SQL
    ... > The company has a policy and states that we have to audit> our databases. ... >>SQL Server MVP ... But supposed if I were to re-start the DB, the>>> audit does not kick off at all coz the trace files still ...
    (microsoft.public.sqlserver.security)
  • Best Practices for Auditing
    ... We would like to audit some sensitive information, ... sys admin role and we want to limit the ability of the sys ... trace, removing records from the trace, etc.), my ...
    (microsoft.public.sqlserver.security)
  • Re: Audit Logs
    ... either does not exist or is not a recognizable trace file. ... This comes from the fact that C2 Audit logging is turned ... on and rolls the file over every 200mb. ... >the imports in a job. ...
    (microsoft.public.sqlserver.security)
  • Re: Possible to track users file system usage?
    ... has without trace. ... If you want to try following through lots of logs ... you could set a access audit for that user only on each and every ... We have a top level employoee who has given his 2 week notice. ...
    (microsoft.public.security)