Tracking down culprit that dropped a trigger in SQL 2000

Question

Tracking down culprit that dropped a trigger in SQL 2000

vince.tran

Old Hand

Points: 347
More actions
July 8, 2009 at 4:06 pm

#206683

Hello Community,
We had an issue with a trigger being dropped from a SQL 2000 production database server. Management is asking if there's a way to trace down the culprit. I've checked the SQL logs but there isn't anything listed. Does anyone have any suggestions going forward on how to set this up either via more granular logging or other means?
What we want to do is to be able to track down activities as far as who was in the database and if there are any activities such as deleting stored procedures or triggers and have that captured. I know this might ramp up the logging activities but we suspect that there is some foul play involved and will need to turn it on (if possible).
Any help would be greatly appreciated.
Thanks,

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

You will need to look at setting up a server side trace that is started using a startup stored procedure.

I'd start by researching SQL Trace and SQL Profiler in Books Online and then asking more detailed questions as you go through the documentation.

vince.tran Old Hand Points: 347 More actions · Answer 2

Thanks for the response. I thought about that too but wasn't sure if I can leave that running all the time for I've heard that profiler/trace is pretty resource intensive and might eat up a lot of disk space if you let it run indefinitely. Is that the case and what has been your or anybody's experience with it?

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

You can also purchase a 3rd party tool such as Idera's Compliance Manager. It will give you everything you want, and can be configured to send you email alerts when specific actions occur.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Running a server side trace is not too intrusive, depending on what you are capturing and can be left running while SQL Server is up and running. Using Profiler, however, could be a problem.

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

Lynn Pettis (7/9/2009)
Running a server side trace is not too intrusive, depending on what you are capturing and can be left running while SQL Server is up and running. Using Profiler, however, could be a problem.

Just made me think ... If a server side trace is started, what happens when the server gets rebooted ? I suppose the trace would need to get restarted ? Is there a way to automate that ?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

A startup stored procedure. You can create a stored procedure in the master database and set it to run on startup.

You should be able to find thin in BOL.

homebrew01 SSC Guru Points: 55548 More actions · Answer 7

Won't the the automatic startup have a problem because the trace file name specified will already exist from before the shutdown ? TraceFile.trc TraceFile_1.trc etc So a new name would need to be dynamically built ?

I'm new at this area, and just set up a trace last week, so any guidance is appreciated ... thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

It will either create a new trace file, trace_2.trc if trace.trc and trace_1.trc already exist, or it may start appending to the current trace file if it hasn't reached its max size. If I remember correctly, it will start a new trace file. I'd have to do some research to be sure, but you don't have to change the name of a trace you start using a startup proc.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

If you were on sql2k5, I would suggest creating a ddl trigger to help monitor for this.

The DDL trigger in 2k5 can monitor for create, alter and drop events related to triggers, as well as many of the same events for other ddl objects.

Since you are on SQL2K, I would go with the trace and startup proc.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 10

If your script is not generating the file name dynamically then you will get the below mentioned error:

Windows error occurred while running SP_TRACE_CREATE. Error = 0x80070050(The file exists.).

Msg 19062, Level 16, State 1, Procedure sp_trace_create, Line 1

Could not create a trace file.

MJ

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Once the trace is setup, all the startup stored procedure needs to do is start the trace. It shouldn't have to be setup every time the server is restarted. I'll have to verify this to be sure.