DDL auditing in sql server2000

Question

DDL auditing in sql server2000

Sathiya priya

Mr or Mrs. 500

Points: 560
More actions
March 11, 2009 at 5:31 am

#99514

Hi,
How to capture ddl changes in sql server 2000.
Regards,
Priya

Viewing 7 posts - 1 through 6 (of 6 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Profiler or a server-side trace.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sathiya priya Mr or Mrs. 500 Points: 560 More actions · Answer 2

Hi,

I need to stroe all the ddl changes in a table(need to capture who has done the changes and when).

I can do this in sql 2005 using ddl triggers. Is that anything similar to that in sql 2000?

Regards,

Priya

Lowell SSC Guru Points: 323518 More actions · Answer 3

definately use some of the log trolling tools out there...i think RedGates SQL Log Rescue for SQL 2000 is/was free, so something like that or profiler are your best bets.

i hesitate to say it, because you could seriously pork your system, but it's possible to put a trigger on the tables in the master database(sysobjects,sysdatabases,syscolumns, etc). I did it on a dev server to prove it could be done....If your trigger fails because it was written poorly, oir for any other reason(permissions, etc), well...i'd guess you'd have to rebuild master.

that's mostly for info purpsoes, stick with the suggested solutions.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sathiya priya Mr or Mrs. 500 Points: 560 More actions · Answer 4

Hi Gail,

Server Side Trace in Sql server 2000 has not captured the objects which is created. Am i missing something 🙁 ?

Regards,

Priya

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Sathiya priya (3/23/2009)
Server Side Trace in Sql server 2000 has not captured the objects which is created. Am i missing something 🙁 ?

What events are you tracing, what filters do you have defined and what objects were created that didn't appear in the trace?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sathiya priya Mr or Mrs. 500 Points: 560 More actions · Answer 6

Hi Gail,

I am trying to capture Create / alter / drop for tables. Trace is capturing the data for alter and drop. But when i try to create the table it doesnt. There is no filter condition.

Regs,

Priya