Re: Auditing changes made to table design (syscolumns table)
From: Andrew J. Kelly (akelly@targitinteractive.com)
Date: 09/10/02
- Next message: Ron Lounsbury: "Re: Using Windows Logins in MSDE"
- Previous message: bouscal: "Access denied on xp_sendmail"
- In reply to: Sean Aitken: "Auditing changes made to table design (syscolumns table)"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "Andrew J. Kelly" <akelly@targitinteractive.com> Date: Tue, 10 Sep 2002 15:32:12 -0400
With out using profiler the only way I know of is to get this info from the
logs. You say that 3rd part tools are too expensive. Well the only tool I
know of to do this really is Log explorer from www.lumigent.com . Yes the
list price for the Profession version is around $395.00. How many hours of
your time will it take before you expend more than that amount of money. It
would only be a few hours of my time and I am sure you could spend days
searching for an alterative and not find anything as useful as that tool for
this type activity. Of coarse it's up to you how you spend your time but
don't expect to find a simple solution to what your asking. There just
isn't one other than this tool.
-- Andrew J. Kelly SQL MVP Targitinteractive, Inc. "Sean Aitken" <seandotaitken@tekelecdotcom> wrote in message news:O6IZuTPWCHA.2476@tkmsftngp09... > Hello all, > We recently had a problem arise where a user managed to change the > security settings for a particular ClearQuest table which managed to cause > problems for other certain users. I researched various transaction log > analysis tools, which proved to be a fruitless effort as the only thing that > seemed capable of reasonably searching and filtering the data would cost us > a few hundred dollars. I continued to dig and found the 'undocumented' > "DBCC LOG (...)" command. This seemed promising, except there were 2 > variations on the parameters, and using SQL 7 seems to supply me with the > LESS flexible ( dbName, ReportingLevel ) parameters. This was a decent > start I thought. Keep in mind I know the database and the tables I would > like to see transactions against, so already I could eliminate 95% of the > returned data. After running the command with a detail level 2, I was able > to see some slightly useful information, among the 420,000 returned rows. I > ran again with level 3, and it never finished. Managed to build a 1.5 Gig > file in my temp directory (after setting output of Query analyzer to 'file') > The first few rows returned showed dates from 11/01. This seemed futile. > So, since the action log couldn't be recovered without unnecessary > expendutures, I began to implement a trigger-based audit solution. First > step, I attempted to write a trigger to log when a change was made to the > syscolumns table in that database. This seemed pretty cool, cuz I could > then log the workstation and the user that performed the change. So my > bubble was busted after reading that you can't create triggers against > system tables. Back to the drawing board. I then considered the profiler, > which was not really an option since it has to intercept and look for > certain criteria against every transaction. (this would include extended > stored proc to do the same) > So, as I sit scratching my brain, can anyone help me find an easy way to > capture who makes changes to table structures, or any other system table for > that matter. > > Any help is GREATLY appreciated! > > Sean Aitken > Tekelec > Morrisville, NC > > (Please reply to the news group, as I have been unable to find postings with > a viable solution thats geared toward my specific problem, and I'm sure > others could benefit) > >
- Next message: Ron Lounsbury: "Re: Using Windows Logins in MSDE"
- Previous message: bouscal: "Access denied on xp_sendmail"
- In reply to: Sean Aitken: "Auditing changes made to table design (syscolumns table)"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
