Post reply

Table Alteration Autiditng

  • April 29, 2004 at 6:40 am

    #160858

    We recently experienced a problem where someone (possibly a DBA) dropped a production table and recreated it without indexes, or truncated it and dropped the indexes. I really don't know what happened.

    My question is: Is there a way to send an alert when a table is dropped or truncated. I'd also like the machine name that issued the TSQL command if at all possible.

     

    Thanks in advance

     

    Doug Heeren

  • May 3, 2004 at 8:00 am

    This was removed by the editor as SPAM

  • May 3, 2004 at 10:42 pm

    #505069

    This ain't easy to monitor. 

    If it was an option, you would put triggers on the relevant system tables BUT this is not supported by MS.

    What you could do is run SQL Profiler and sent the output to a table.  You could then analysis the commands that have actually been run and trigger whatever action you need.

    I don't think that any of the applications out there that extract data from the transaction logs will give you the workstation name as this isn't in the log.

    Cheers

    Stephen

  • May 4, 2004 at 10:07 am

    #505159

    Take a look at Lumigent Schema Alert.

    Desc:

    Lumigent® Schema AlertTM is a utility for real-time detection of database changes. This utility tracks all activity of type CREATE TABLE, DROP TABLE, ALTER TABLE, CREATE DATABASE, or DROP DATABASE on the selected SQL server. When one of these commands is detected, the Schema Alert utility generates an alert. These alerts appear in the Schema Alert window, and can also be placed in the Windows Event Log and/or sent to a specified email address.

    Unfortunatyl it does NOT tell you which computer was used but it WILL tell you what user issued the statement. So if you have good user setup it seems to fit your needs quite well but if everyone are using sa...

    Regards, Hanslindgren

  • May 5, 2004 at 5:30 am

    #505279

    Thank you for the product information. I have recently looked at some of their products, but did not see this one.

     

    Doug

  • June 24, 2004 at 3:05 pm

    #511877

    It is listed as freeware under the downloads option and not under the regular product section. I have used it before and found it to be effective. Here is the link

    http://www.lumigent.com/downloads/schemaalert/

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

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