Post reply

Auditing DDL in SQL2K5

  • December 16, 2009 at 10:25 am

    #137678

    I have an odd issue occurring on a number of SQL Servers. There is one procedure that I had created and added to a few database servers' MSDB databases. The procedure is fairly simple, it's running xp_cmdshell to execute some commands and return some information back for monitoring purposes. The issue I am having is that every time I add this procedure to a certain handful of servers, it disappears. A few of the servers have the procedure disappear at a certain time, a few other seem to have it disappear almost instantly.

    I am wondering if someone can tell me how I can write a DDL trigger script that will log any drop procedure statements to a table so I can see what is going on and who (or what process) is doing this.

    I tried profiler tracing but I don't see anything coming up. One reason for that might be because many of the procedures are encrypted and I don't know if encrypted statements will show up in the SQL profiler.

    Thanks,

    John

  • December 16, 2009 at 10:40 am

    #1093194

    the default trace is helpful for this exact sort of thing, as it captures all the DDL changes

    there's even a built in report for it.

    go to Object explorer in SSMS, right click on the database in question that you know lost the procedure, and choose Reports>>Schema Changes History.

    it queries the default trace for that specific database, and gives output similar to this:

    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!

  • December 16, 2009 at 11:21 am

    #1093217

    A quick note - that report will need to be opened under "Standard Reports" first. Once done, then it will cache to the context menu shown in the graphic.

    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

  • December 16, 2009 at 11:26 am

    #1093223

    Thanks guys, that is very helpful

Viewing 4 posts - 1 through 3 (of 3 total)

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