Re: User and Login auditing

From: Andrew J. Kelly (akelly@targitinteractive.com)
Date: 06/26/02 

From: "Andrew J. Kelly" <akelly@targitinteractive.com>
Date: Wed, 26 Jun 2002 14:41:02 -0400

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: Problem with SQL Server [Solution]
    ... Apparently my sql server was not set to mixed mode authentication. ... > with the Trace Properties. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: URGENT deadlock question
    ... I have a deadlock template that I use with the following ... > SQL: Statement Completed ... > "Trace flags remain enabled in the server until disabled by executing ...
    (microsoft.public.sqlserver.server)
  • Re: SQL 7 Traces
    ... Profiler 2K can generate the xp calls for a defined trace in 7 format. ... Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL 7 Traces
    ... With SQL 7, you can simply use the SQL Profiler to trace the server. ... Profiler is a GUI tool that hooks into server events inside SQL Server. ...
    (microsoft.public.sqlserver.programming)
  • Re: Problem with SQL Server
    ... I am running the SQL Profiler on the machine that is running SQL Server. ... I added Audit Login Failed Filter. ... I've attached the trace. ...
    (microsoft.public.dotnet.framework.adonet)