Wednesday, May 11, 2011

Msg 3701, Level 11, State 5, Line 1 Cannot drop the trigger ‘ ‘, because it does not exist or you do not have permission.

 

I got the error message below, when i was playing with DDL Trigger on SQL Server 2008.
The trigger exists, i have cross verified querying;

select *
from master.sys.server_triggers
where name = 'connection_limit_trigger'


select objectproperty(1435152158, 'isTrigger') isTrigger
       ,objectproperty(1435152158, 'ownerid') ownerID

Error Message:

Msg 3701, Level 11, State 5, Line 1
Cannot drop the trigger 'connection_limit_trigger', because it does not exist or you do not have permission.

Resolution:

After scratching my head, i found the resolution. It is simple !

If the trigger is DATABASE Level scope use below

Drop TRIGGER connection_limit_trigger on Database

Otherwise if it is Server Level scope please try below;

Drop TRIGGER connection_limit_trigger on all server

Cause:
The lost bold command i.e. scope level makes the different.
When we use DDL Trigger and trying to drop using the DROP command, we should match the scope level of the trigger when it created.

2 comments:

scott olander said...

Thank you! This was a lifesaver for me!

toink said...

Try this :

IF OBJECT_ID ('[db_name.[table_name','TR') IS NULL
DROP TRIGGER dbo.[trigger_name];
GO