Re: User and Login auditing

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

From: "Gabe" <Lajma@fibertel.com.ar>
Date: Wed, 26 Jun 2002 16:19:50 -0700

Sue and Andrew, thanks for the help. I suppose that finally the best way is
to take a snapshot of the system tables periodically and compare them to see
what has changed, been added or missing. (the actual table now)

I will not apply the triggers as you reccomend and will take a look at the
URL proposed...

Thanks for everything

Regards

Gabe

"Andrew J. Kelly" <akelly@targitinteractive.com> wrote in message
news:e8ibqDUHCHA.2132@tkmsftngp10...
> I agree 100% with Sue on not touching the system tables. Maybe this will
do
> what you want.
>
> http://www.sqlpower.com/
>
> --
> Andrew J. Kelly SQL MVP
> Targitinteractive, Inc.
>
>
>
> "Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
> news:tbvjhu8om1h9akf46cja13aik59bu9lc6u@4ax.com...
> > Really bad I idea. You can do some things to allow you to
> > create triggers on system tables and then you will find out
> > that the tables don't populate or update the way you think
> > they do - only updates through standard T-SQL will fire the
> > triggers. And you will end up with an unsupported system. If
> > you feel you will never need to apply any service packs,
> > security updates, or hotfixes and that you will never need
> > to contact MS support and you will never upgrade the server,
> > then I suppose you could take your chances and go this route
> > and hope that your employer doesn't mind you putting the SQL
> > Server box in this position.
> >
> > -Sue
> >
> > On Wed, 26 Jun 2002 14:29:56 -0700, "Gabe"
> > <Lajma@fibertel.com.ar> wrote:
> >
> > >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
    ... I agree 100% with Sue on not touching the system tables. ... > 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: ISQL query fails after reboot
    ... Thank you for your help Sue. ... error messages, configurations, exact steps to reproduce, ... server and tried it on a plain vanilla sql server with no success. ... From the client side we use isql queries. ...
    (microsoft.public.sqlserver.connect)
  • Re: Group Permissions
    ... make sure that the "Server" has its password changed manually as well(of ... You have four users - Kerry, Malke, Bill, and Sue. ... On the pseudo-server, make Malke, Bill and Sue just plain users. ... the inbound concurrent connections limit. ...
    (microsoft.public.windowsxp.basics)
  • Re: cant auto-forward to non-exchange address
    ... If it's blocked at the server, you as a user cannot get around it. ... Sue Mosher, Outlook MVP ... "sherry" wrote in message ... > unfortunately my sysadmin (also the exchange admin) thinks ...
    (microsoft.public.outlook.general)
  • Re: User and Login auditing
    ... To actually monitor login changes, ... >>Sue and Andrew, thanks for the help. ... >>> I agree 100% with Sue on not touching the system tables. ... >>> Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.security)