Sql-server – Why can’t trigger be dropped from database

sql serversql-server-2012trigger

One of the databases I manage is provided by my company's vendor which we restore in order to pull data out for our data warehouse. I'm writing a stored procedure to go through the database users and remove all of the users from the vendor. There is one user, we'll call it userx, which can't be removed because there's a trigger which is set to execute as this user, we'll call it trg_write_something. When I run:

DROP TRIGGER trg_write_something

I get these error messages:

Msg 50000, Level 16, State 1, Procedure PreserveTrigger, Line 18

Trigger [trg_write_something] cannot be dropped from the database.

Msg 3609, Level 16, State 2, Procedure trg_write_something, Line 4

The transaction ended in the trigger. The batch has been aborted.

If I try to alter the trigger I get the same error.

Best Answer

The transaction is being rolled back in the DDL trigger, PreserveTrigger, not the trigger you're trying to drop, trg_write_something. This has nothing to do with the code in the table-level trigger, and nothing to do with permissions either. A sysadmin doesn't get to blindly bypass things like DDL triggers, though it is possible for the code in the trigger to have been written to check the user's security level before deciding what to do (but that is uncommon). It is, of course, very easy for a sysadmin to disable or drop a DDL trigger, obviously.

You're seeing these specific errors because a user-defined error message is being raised within the DDL trigger, followed by an explicit ROLLBACK TRANSACTION;. In a comment above, I mentioned that it could be because of a runtime error, but that is not enough to generate the pair of error messages you are receiving (or force an explicit rollback, unless you somehow managed to get a DDL trigger to raise a severe enough error level to terminate the batch - 16 wouldn't do that).

You can discover this DDL trigger as follows:

SELECT name
  FROM sys.triggers
  WHERE name = N'PreserveTrigger'
  AND parent_class_desc = N'DATABASE';

Or all DDL triggers like this:

SELECT name
  FROM sys.triggers
  WHERE parent_class_desc = N'DATABASE';

(You can also find them in Object Explorer under Programmability > Database Triggers.)

And you can see the code this way (which you should probably do before you drop it - maybe it's there for a reason, even if it is currently standing in your way of dropping this specific trigger):

SELECT m.[definition]
  FROM sys.sql_modules AS m
  INNER JOIN sys.triggers AS t
  ON m.[object_id] = t.[object_id]
  WHERE t.name = N'PreserveTrigger';

Or by right-clicking in Object Explorer and selecting Script Database Trigger As > CREATE To > New Query Editor Window.

You can disable the trigger temporarily like this...

DISABLE TRIGGER PreserveTrigger ON DATABASE;

...which, again, is probably better/safer than just dropping it.

Related Question