Deleting logon trigger.
-
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é
-
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.
-
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é
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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é
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Found it, - thanks.
And yes you are right about the ALL SERVER was missing.
/René
-
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?
-
Reverse the logic
If not exists () Create blank trigger
;
ALTER TRIGGER...
P.S. Please start a new thread next time.
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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!
-
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,.
-
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.
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
THANKS FOR THIS!!! I HAVE BEEN GOING CRAZY!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply