Access extended properties for dropped object in DDL triggers

Question

In Sql Server I want to prevent users from dropping objects marked with a particular extended property. I thought this could easily be accomplished with a DDL trigger on the DROP event. As it turns out these triggers are fired after the object has been dropped and therefore it's no longer possible to access the extended properties and they are not available in the event data.

Is there any way to access extended properties for a dropped object in a DDL trigger?

Code below does not work since the extended properties have already been deleted:

CREATE TRIGGER PreventDeletionOfAutogeneratedTriggers ON DATABASE 
FOR DROP_TRIGGER
AS
    DECLARE @TriggerName sysname    =     EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
    DECLARE @TriggerSchema sysname  = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname')
    DECLARE @ObjectId int           = OBJECT_ID(QUOTENAME(@TriggerSchema) + '.' + QUOTENAME(@TriggerName))
IF EXISTS (
    SELECT
        *
    FROM
        sys.extended_properties
    WHERE
        major_id = @ObjectId AND
        name = 'Autogenerated'
)
BEGIN
    RAISERROR ('Cannot drop triggers that are autogenerated.',16, 10)
    ROLLBACK
END 
GO

Answer 1

Currently, DDL Triggers only fire AFTER the DDL statement is complete, meaning that you will not have access to the appropriate metadata in order to force a ROLLBACK.

INSTEAD OF Triggers do not exist for DDL statements, which is essentially what you would need to accomplish this task.

You can vote for INSTEAD OF DDL Triggers here: https://connect.microsoft.com/SQLServer/feedback/details/243986

My suggestion would be to put these objects in a separate schema and set permissions on the schema, or to lock them down by user Roles and Permissions.

Here is a link to more information on DDL Triggers. https://technet.microsoft.com/en-us/library/ms175941(v=sql.120).aspx