August 5, 2008 at 12:29 pm
I've discovered something that I'm very concerned about and need a reality check. On SQL Server 2000, if I'm correct (and I REALLY hope I'm making a mistake!) it doesn't seem to write anything to the log if you delete a database or take it offline.
I performed the following steps:
Confirm server is set to Log All and RECONFIGURE.
Create a database (LogTest).
Take the DB offline.
Delete the DB.
Check the log.
You see the database startup, but you don't see the offline or delete.
Now, I know that I should have absolute control over who can create or delete databases, and I do: it's down to me and the ERP manager. But the point of this little exercise is a what if: what if someone has somehow managed to get into my system and does some dirty deeds (done dirt cheap). I rely on my logs to tell me things, and in this case, it's not telling me everything that I want to know.
I repeated the above steps in 2005 (I don't have 2008 installed anywhere, so I can't test there at this time, though I guess I can download it at home and try it) and once again, the log showed the database created, but not deleted. It did show the DB going offline, so at least that's there.
So am I missing something? I checked the server logs, and they don't show any of this activity. I'm just really concerned that deletes are not logged.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
August 5, 2008 at 12:44 pm
How are you deleting the database? The DROP Database command? When it is offline you can just delete the .mdf file and that won't be logged anywhere.
As I note I duplicated your test on 2005 and did see the same thing. It is logged on creation and change of offline status.
In 2005 you could use DDL triggers to log the DROP Database command. Perhaps C2 or Common Criteria (2005) auditing will work.
A last one could be run a server trace that just logs the DROP DATABASE command.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 5, 2008 at 12:47 pm
I did everything through Enterprise Manager.
Yeah, I considered the concept of triggers on sysobjects to see such activity, but I don't like the concept of modifying system databases and objects like that.
I don't work much with Trace, I'll have to look into that. We're in the process of setting up a virtual test environment, at which point testing C2 might be possible.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
August 5, 2008 at 12:56 pm
In 2005 you would not have to put a DML trigger on sysobjects just a DDL trigger in master.
C2 auditing is basically a server-side trace that starts when SQL Server starts up and shuts down the SQL Server if it doesn't run. You could accomplish the same thing with a user-defined trace that will keep the server running if it has problems.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply