1

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Robin Palm
  • 111
  • 6
  • "In Sql Server I want to prevent users" - that's normally accomplished through permissions. Users should require only read and limited execute rights – Mitch Wheat Mar 17 '15 at 11:46
  • Yes you are of course right, but in this case "users" are sysadmins who should be informed about the correct way to delete these objects, not prevented from doing so. – Robin Palm Mar 18 '15 at 14:25

1 Answers1

0

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

rhholt
  • 419
  • 3
  • 10
  • I think your schema suggest is good but it seems Robin may want to restrict dropping triggers, but not necessarily the parent table. Both must be in the same schema so locking down at the schema level is problematic. – Dan Guzman Mar 17 '15 at 12:38
  • Thanks for your suggestions but actually I don't want to completely block the possibility to drop these objects. They are generated by a tool and the usual way to remove them would be using that tool and the trigger is there to inform about that. Anyway it seems there is no way to accomplish what I want so it will have to wait until that connect ticket is done (any decade now :)). – Robin Palm Mar 18 '15 at 14:22