Re: User and Login auditing

From: Gabe (Lajma@fibertel.com.ar)
Date: 06/26/02 

From: "Gabe" <Lajma@fibertel.com.ar>
Date: Wed, 26 Jun 2002 14:29:56 -0700

I apprecciate your help Sue, but I think I will have to definitely create
triggers on system tables (sysusers and syslogins) since I do not want to
run server side traces either to tables or files. This would degrade
performance on the server since I would have to run them forever to track
all the changes I need, especially since I have 5 databases and want to
track all of them.

I really dont want to create these since MS doesnt reccomend, but I guess
there is no other way if I want to know each time a user or logins is
modified added or deleted from different databases.

By the way, I am running SQL 7. I just wante to know if anyone had ever run
into this problem. Another possible solution is to copy the different
sysuser and syslogin tables periodically to a different site and compare
tables, to look for changes..

I dont know what the best and easiest solution is...

Thanks for your help Sue

Gabe

"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
news:rbsjhu4474odttmmpb5ff0lrphg0rif6pd@4ax.com...
> Gabe,
> You could use server side traces to trace the same things
> instead of running Profiler. But again, how you implemented
> for your needs depends on what version of SQL Server you are
> running. You could put triggers on your trace table.
> If on SQL 7, you can use extended stored procedures to trace
> directly to a table. take a look at:
> INF: How to Trace in SQL Server by Using Extended Stored
> Procedures (Q258990)
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q258990
> For SQL 2000 you might be more limited with client side
> traces as you need to do this trace to a file. But you could
> schedule something to load the files into a table:
> INF: How to Programmatically Load Trace Files into Tables
> (Q270599)
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q270599
>
> -Sue
>
> On Wed, 26 Jun 2002 13:49:11 -0700, "Gabe"
> <Lajma@fibertel.com.ar> wrote:
>
> >Thx Sue, but I need to constantly be notified when someone
> >adds/modidesd/delete a user or login. Thats why I was thinking of
triggers
> >insted of Profiler, since I dont want to run Profiler on a minute to
minute
> >basis....
> >
> >I would like to be able to report what users and logins I had yesterday
and
> >compare them to todays
> >users and logins. I suppose I have to track these changes with user
defines
> >tables right ?
> >
> >It would be easier with Triggers on sysuers and syslogin,s but I have
never
> >seen anyone do this and besides, MS does not reccomend it...
> >
> >Any suggestions ?
> >
> >"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
> >news:skqjhukqio6htda4r15h8purgdprptkbas@4ax.com...
> >> You can use Profiler to do this. If you are on SQL Server
> >> 2000, look at Profiler and the Securiity Audit Event
> >> classes. On SQL 7, you could still monitor for executions of
> >> sp_grantdbaccess, sp_denydbaccess, sp_grantlogin,
> >> sp_addlogin, and sp_denylogin.
> >>
> >> -Sue
> >>
> >> On Wed, 26 Jun 2002 13:17:59 -0700, "Gabriel"
> >> <Lajma@fibertel.com.ar> wrote:
> >>
> >> >Does anyone know how I can audit user and login adding, deleting and
> >> >modification ?
> >> >
> >> >I need to know when and who is adding users and logins to my server
and
> >> >databases..
> >> >
> >> >I was thinking of triggers on sysuers and syslogins tables, but MS
does
> >not
> >> >reccomend this...
> >> >
> >> >Any hints ??
> >> >
> >> >Thx
> >> >
> >> >Gabe
> >> >
> >>
> >
>

Relevant Pages

  • Re: User and Login auditing
    ... create triggers on system tables and then you will find out ... and hope that your employer doesn't mind you putting the SQL ... Server box in this position. ... You could put triggers on your trace table. ...
    (microsoft.public.sqlserver.security)
  • Re: Tracking user activities
    ... >> I'd like these settings to persist permanently and the event records to ... > In SQL 2000, this is not very simple to do. ... > Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... select Script Trace. ...
    (comp.databases.ms-sqlserver)
  • Re: How to look at parameter string
    ... I am tracing, but the problem I can't trace (at least, I haven't figured out ... string it is putting together to send to Sql. ... >> The problem is not coming from Sql Server but from my page. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: C2 SQL auditing
    ... Andrew J. Kelly SQL MVP ... Do you suggest any third party tools for SOX compliance? ... I would like to off load the server side trace to the SOX team ... when I stop SQL server then my trace file gets populated. ...
    (microsoft.public.sqlserver.security)
  • Re: SQL logins logged where
    ... you can't trace directly to a table but can use the> fn_trace_gettable function to load the trace file into a> table. ... If you are using SQL Server 7, use> xp_trace_setqueuedestination to set the destination to a> table. ... Security Tab does not give too many ...
    (microsoft.public.sqlserver.security)