April 18, 2007 at 3:49 am
Dear all,
We are currently having some problems with someone getting into the back end of one of our systems, and deleting data from the database. I wondered if there is any way of putting an audit trail on so that I can monitor which logins are going on to the database and what they have done, i.e deletes, updates etc. The database is on SQL Server 2000.
Many thanks!
Paula
April 18, 2007 at 6:10 am
I would be inclined to use Profiler for this. I think that would be the fastest way you can get this set up, and normally these type of situation getting a quick result might be important. Then you can start thinking more long term logging of data after that, such as triggers to record changes (of course if they can delete data they might also be able to delete from the audit table depending on their permissions).
Is this suspected to be malicious deletes, or simply someone that does not know any better but should have delete permissions to do their job? If malicious I would start hardening up security.
April 18, 2007 at 6:12 am
In the past I have used a couple of methods. One would be to implement transaction log backups and then use one of the many tools out there to scrape the logs looking for specific incidents.
The second I have used is by running a trace file against the database collecting specific events and then dumping the file into a table and running queries against it. Sounds easy but there is some setup involved, plus there is the potential for some performance hit in doing this. If you're interested let me know and I can give you the scripts and code that I used to set this up.
April 18, 2007 at 6:13 am
Many thanks for this. I'll have a think around it and see what will work best for us.
Thanks,
Paula.
April 19, 2007 at 1:25 am
I actually found this site really helpful:
http://www.auditdatabase.com/Index.htm
They have an online too that you can input your table definition into and it spits out scripts to create triggers to log audit data to a table. Nice!
Catherine
Catherine Eibner
cybner.com.au
April 19, 2007 at 9:28 am
Hi, just a reminder to use NOLOCK hints reading the trace or audit tables.
"oops"
jg
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply