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 * select objectproperty(1435152158, 'isTrigger') isTrigger |
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.
3 comments:
Thank you! This was a lifesaver for me!
Try this :
IF OBJECT_ID ('[db_name.[table_name','TR') IS NULL
DROP TRIGGER dbo.[trigger_name];
GO
(Msg 3701, Level 11, State 5) Cannot drop the table '#SVer', because it does not exist or you do not have permission.
I am getting this error for an read only id often, the read only id just do select cmds
however we keep getting this error on our SIEM any one can shed some lights
MS SQL 2016
Post a Comment