hi, i have created a DDL trigger with encryption for to restrict users from modify any table / proc / functions in my database. but from SQL Server Management studio user can directly drop my ddl trigger itself.
How to avoid this? pls give solution.
with regards Kiran K
You can't avoid it always. If a user has permissions to delete your DDL trigger, then they can delete it. You could create another DDL trigger to stop the first one from being deleted, but they could then just delete that one.
That having been said, you could DENY ALTER ANY DATABASE DDL TRIGGER permission to the database users, which would prevent the DDL trigger from being dropped.
Someone will only be able to drop the trigger if they have the required permission in that database. Setting the security so that they are not allowed to drop objects will prevent your trigger being removed.
