Home / server / Questions / 98354
Accepted
Matt Rogish
Asked: 2009-12-31 12:52:43 +0800 CST

SQL Server 2005 - Deny DROP TRIGGER permissions

  • 772

We have a custom trigger on a 3rd party app table to help us audit changes. Occasionally that trigger is removed. Looking thru the traces it shows that the 3rd party app is dropping it (for no good reason). The Vendor acknowledges it does this "from time to time" to ensure that no one mucks with their database and is not willing to change their app (understandable).

Okay, I get this, but we really need that trigger. Is there a way to disable drop trigger to their app login for this table/trigger? I can't deny ALTER on the database because their app does create/drop tables. Thanks!

sql-server

1 Answers

  1. Best Answer

    There's no way to do that, but you might be able to create a ON SERVER or ON DATABASE DDL trigger that recreates the trigger after their software drops it.

    More info on DDL triggers: http://msdn.microsoft.com/en-us/library/ms186406.aspx

    • 2