SQLServerCentral / Discuss Content Posted by S. Srivathsani / Article Discussions / Article Discussions by Author / Understanding DDL Triggers in SQL Server 2005 / Latest Posts

RE: Understanding DDL Triggers in SQL Server 2005

lucassouzace — Mon, 23 Feb 2009 06:14:01 GMT

hm, is good script but, because: DB_User, Event_Typeis null? is not LOGIN of user? por example: i create login:create login lucas with password = '123'i connect with lucasnow, i create table:create table tbl ( id int )DB_User: lucas ??

RE: Understanding DDL Triggers in SQL Server 2005

paul2g2 — Thu, 21 Aug 2008 10:42:59 GMT

can Iplease haver the command for trigger and stored procedures and the standard format for querying tablesthank you

RE: Understanding DDL Triggers in SQL Server 2005

Charles Kincaid — Fri, 06 Jul 2007 08:02:00 GMT

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

How to force everything to go through this method so that everything is tracked.
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.

RE: Understanding DDL Triggers in SQL Server 2005

Jurgen H. — Fri, 06 Jul 2007 00:36:00 GMT

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

RE: Understanding DDL Triggers in SQL Server 2005

John Kanagaraj — Thu, 05 Jul 2007 17:24:00 GMT

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

RE: Understanding DDL Triggers in SQL Server 2005

Bhavin Patel-257492 — Thu, 05 Jul 2007 10:23:00 GMT

I am confused with what you told in previous post.

RE: Understanding DDL Triggers in SQL Server 2005

Charles Kincaid — Thu, 05 Jul 2007 10:15:00 GMT

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

Create triggers that block all changes on tables, etc.
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.

RE: Understanding DDL Triggers in SQL Server 2005

Bhavin Patel-257492 — Thu, 05 Jul 2007 09:01:00 GMT

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

RE: Understanding DDL Triggers in SQL Server 2005

Cedric Cullingworth — Wed, 04 Jul 2007 02:27:00 GMT

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:

CREATE TRIGGER Trig_DBEvents ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTSAS INSERT master.dbo.ddl_all_databases ( DateTime, LoginName, DBName, UserName, EventType, CommandText ) SELECT GETDATE(), EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(50)'), EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(50)'), EVENTDATA().value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(50)'), EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(50)'), EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(1024)')GO

RE: Understanding DDL Triggers in SQL Server 2005

Jurgen H. — Wed, 04 Jul 2007 00:42:00 GMT

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

Understanding DDL Triggers in SQL Server 2005

srivathsani-296624 — Wed, 14 Mar 2007 13:19:00 GMT

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