Understanding DDL Triggers in SQL Server 2005

  • Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/sSrivathsani/2927.asp

  • It's a pitty it does not cover renaming columns, tables...

  • Most of the code samples are untested and need fixing if you want to see them work.

    Here's what I've settled on to cover most activities - created in all mission-critical databases:



        INSERT master.dbo.ddl_all_databases









        SELECT  GETDATE(),







  • where do I create trigger? in master database? it will fire where any new database is created,right?

  • I can see trouble brewing here.  Would it be possible to:

    1. Create triggers that block all changes on tables, etc.
    2. Create a trigger that blocks changes on all triggers?

    How would one every get out of this?

    Another reason to tighly control such things.  A ticked off admin could do this and then leave.  You might not find it for a long time.  I have production databases that have not had a DDL change in years.


    ATBCharles Kincaid

  • I am confused with what you told in previous post.

  • Oracle has had this functionality for a loooong time. As a result, you might want to look at how Oracle databases use the DDL (and other) System level triggers.

    For myself, I have created a DDL trigger in Oracle's SYS schema to record all changes including what object changed when, and by whom. As well, I have LOGIN/LOGOFF triggers that can be used to track who logged in, from where and if they changed any data (track COMMITs/rollbacks from Oracle's DMV)

    John Kanagaraj

  • As i already told , if you use the sp_rename stored procedures these triggers do nothing, nada.


  • Thank you.  You just answered two questions for me at the same time:

    1. How to force everything to go through this method so that everything is tracked.
    2. How to get around the "lock out" that I discribed.

    You restrict the use of sp_rename.  This forces everybody else to use ALTER TABLE, ALTER COLUMN, etc. to make changes.  If your logon still has access to sp_rename, etc. this gives you a back door.

    ATBCharles Kincaid

  • can Iplease haver the command for trigger and stored procedures and the standard format for querying tables

    thank you

  • hm, is good script but, because: DB_User, Event_Type

    is null? is not LOGIN of user?

    por example: i create login:

    create login lucas with password = '123'

    i connect with lucas

    now, i create table:

    create table tbl ( id int )

    DB_User: lucas


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

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