March 13, 2011 at 11:21 am
Hi
I have been testing logon trigger from the following link: http://msdn.microsoft.com/en-us/library/bb326598.aspx
Everything works fine, I got my results but now I can't drop the trigger. I get the following error:
Msg 3701, Level 11, State 5, Line 1
Cannot drop the trigger 'dbo.connection_limit_trigger', because it does not exist or you do not have permission.
I can ALTER the trigger but thats all. I even tried to log in as SA event though I am sysadmin on the server.
Any ideas?
/René
March 13, 2011 at 11:44 am
Without seeing the code for the trigger, it is a bit hard to know why you can't delete it. Sounds like it may be keeping you from doing the delete.
March 13, 2011 at 12:18 pm
The executed code are:
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'navi'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'navi' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'navi') > 3
ROLLBACK;
END;
The user navi is sysadmin.
/René
March 13, 2011 at 12:19 pm
And what code are you using to drop it?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 13, 2011 at 12:32 pm
Sometimes one’s mind plays you a trick. In the attempt to reproduce I did manage to find the right way:
DROP TRIGGER connection_limit_trigger ON ALL SERVER
did it.
Thanks for your help to push me into thinking.
Is there a place to see the logon trigger from SSMS?
/René
March 13, 2011 at 12:33 pm
Server Objects -> Triggers for server level triggers, Programability -> Database triggers for database scoped triggers.
My guess, on the failed drop you left off the 'ALL Server'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 13, 2011 at 12:39 pm
Found it, - thanks.
And yes you are right about the ALL SERVER was missing.
/René
December 5, 2011 at 9:38 am
May I expand a little bit on the question. How about conditional drop of the trigger?
Let's assume that I need a script which would recreate a trigger. So, if trigger exists, it has to be dropped. According to MSDN the code should be something like this:
IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class = 0 AND name = 'connection_limit_trigger')
DROP TRIGGER connection_limit_trigger ON ALL SERVER
GO
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'navi'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'navi' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'navi') > 3
ROLLBACK;
END;
Yet, for some reason the LOGON trigger on ALL SERVER is not listed in sys.triggers.
So, what the check query for LOGON trigger should be?
December 5, 2011 at 9:40 am
Reverse the logic
If not exists () Create blank trigger
;
ALTER TRIGGER...
P.S. Please start a new thread next time.
December 5, 2011 at 9:50 am
zorge (12/5/2011)
Yet, for some reason the LOGON trigger on ALL SERVER is not listed in sys.triggers.
No mysterious reason. Sys.triggers is for database-scoped triggers. You'll find server-scoped triggers in sys.server_triggers
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 5, 2011 at 9:52 am
GilaMonster (12/5/2011)
zorge (12/5/2011)
Yet, for some reason the LOGON trigger on ALL SERVER is not listed in sys.triggers.No mysterious reason. Sys.triggers is for database-scoped triggers. You'll find server-scoped triggers in sys.server_triggers
Please note, amost 1 YO thread 😉
Yes, I know, good point to post!
December 5, 2011 at 12:20 pm
I don't see the benefit of creating a blank trigger. If trigger already exists creation of blank trigger would fail. If trigger does not exist drop trigger would fail.
I was considering creating a new thread, but thought that the issues are closely related,.
December 5, 2011 at 12:25 pm
Thanks. That is exactly what I was looking for.
The MSDN article I was referring to (http://msdn.microsoft.com/en-us/library/ms173497.aspx) states that:
Because DDL triggers are not schema-scoped and, therefore do not appear in the sys.objects catalog view, the OBJECT_ID function cannot be used to query whether they exist in the database. Objects that are not schema-scoped must be queried by using the appropriate catalog view. For DDL triggers, use sys.triggers.
That was the reason for my confusion. Thanks again.
December 5, 2011 at 2:02 pm
ddl triggers are in sys.triggers as they are database-scoped triggers. Server triggers however are not. Which database's sys.triggers would they go into?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 5, 2015 at 12:02 pm
THANKS FOR THIS!!! I HAVE BEEN GOING CRAZY!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply