Re: User and Login auditing
From: Sue Hoegemeier (Sue_H@nomail.please)
Date: 06/26/02
- Next message: Raks: "dtsrun fails inside a job"
- Previous message: Sue Hoegemeier: "Re: Default Database Missing, Unable to Connect"
- In reply to: Gabe: "Re: User and Login auditing"
- Next in thread: SQL Guy: "Re: User and Login auditing"
- Reply: SQL Guy: "Re: User and Login auditing"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: Sue Hoegemeier <Sue_H@nomail.please> Date: Wed, 26 Jun 2002 15:37:29 -0600
That's good - anything but updating the system tables <g>.
Hope I didn't sound too harsh - just hate to see someone put
themselves in such a bad position.
Good luck with it Gabe-
-Sue
On Wed, 26 Jun 2002 16:19:50 -0700, "Gabe"
<Lajma@fibertel.com.ar> wrote:
>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
>> > >> >> >
>> > >> >>
>> > >> >
>> > >>
>> > >
>> >
>>
>>
>
- Next message: Raks: "dtsrun fails inside a job"
- Previous message: Sue Hoegemeier: "Re: Default Database Missing, Unable to Connect"
- In reply to: Gabe: "Re: User and Login auditing"
- Next in thread: SQL Guy: "Re: User and Login auditing"
- Reply: SQL Guy: "Re: User and Login auditing"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
