Re: User and Login auditing

From: Sue Hoegemeier (Sue_H@nomail.please)
Date: 06/26/02

From: Sue Hoegemeier <Sue_H@nomail.please>
Date: Wed, 26 Jun 2002 11:50:51 -0600

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.


On Wed, 26 Jun 2002 14:29:56 -0700, "Gabe"
<> 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
>"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
>> 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)
>> 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)
>> -Sue
>> On Wed, 26 Jun 2002 13:49:11 -0700, "Gabe"
>> <> 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
>> >insted of Profiler, since I dont want to run Profiler on a minute to
>> >basis....
>> >
>> >I would like to be able to report what users and logins I had yesterday
>> >compare them to todays
>> >users and logins. I suppose I have to track these changes with user
>> >tables right ?
>> >
>> >It would be easier with Triggers on sysuers and syslogin,s but I have
>> >seen anyone do this and besides, MS does not reccomend it...
>> >
>> >Any suggestions ?
>> >
>> >"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
>> >
>> >> 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"
>> >> <> 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
>> >> >databases..
>> >> >
>> >> >I was thinking of triggers on sysuers and syslogins tables, but MS
>> >not
>> >> >reccomend this...
>> >> >
>> >> >Any hints ??
>> >> >
>> >> >Thx
>> >> >
>> >> >Gabe
>> >> >
>> >>
>> >