Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Deleting logon trigger. Expand / Collapse
Author
Message
Posted Sunday, March 13, 2011 11:20 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 5, 2013 4:33 AM
Points: 14, Visits: 151
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é
Post #1077471
Posted Sunday, March 13, 2011 11:44 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 23,086, Visits: 31,626
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1077476
Posted Sunday, March 13, 2011 12:18 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 5, 2013 4:33 AM
Points: 14, Visits: 151
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é
Post #1077480
Posted Sunday, March 13, 2011 12:19 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 42,493, Visits: 35,562
And what code are you using to drop it?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1077482
Posted Sunday, March 13, 2011 12:32 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 5, 2013 4:33 AM
Points: 14, Visits: 151
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é
Post #1077485
Posted Sunday, March 13, 2011 12:33 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 42,493, Visits: 35,562
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1077486
Posted Sunday, March 13, 2011 12:39 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 5, 2013 4:33 AM
Points: 14, Visits: 151
Found it, - thanks.

And yes you are right about the ALL SERVER was missing.

/René
Post #1077488
Posted Monday, December 5, 2011 9:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 13, 2012 11:17 AM
Points: 3, Visits: 21
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?
Post #1216429
Posted Monday, December 5, 2011 9:40 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:48 AM
Points: 21,388, Visits: 9,604
Reverse the logic

If not exists () Create blank trigger

;
ALTER TRIGGER...


P.S. Please start a new thread next time.
Post #1216435
Posted Monday, December 5, 2011 9:50 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 42,493, Visits: 35,562
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1216449
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse