Deleting logon trigger.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Deleting logon trigger.

14 posts, Page 1 of 2

1 2 »»

Deleting logon trigger.

Rate Topic

Display Mode

Topic Options

Author

Message

4R4

Posted Sunday, March 13, 2011 11:20 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 03, 2013 1:13 AM
Points: 14, Visits: 148

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

Lynn Pettis

Posted Sunday, March 13, 2011 11:44 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:14 PM
Points: 21,832, Visits: 27,862

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.

Cool

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

4R4

Posted Sunday, March 13, 2011 12:18 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 03, 2013 1:13 AM
Points: 14, Visits: 148

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

GilaMonster

Posted Sunday, March 13, 2011 12:19 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:31 AM
Points: 38,121, Visits: 30,405

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

4R4

Posted Sunday, March 13, 2011 12:32 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 03, 2013 1:13 AM
Points: 14, Visits: 148

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

GilaMonster

Posted Sunday, March 13, 2011 12:33 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:31 AM
Points: 38,121, Visits: 30,405

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

4R4

Posted Sunday, March 13, 2011 12:39 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 03, 2013 1:13 AM
Points: 14, Visits: 148

Found it, - thanks.

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

/René

Post #1077488

zorge

Posted Monday, December 05, 2011 9:38 AM

Forum 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

Ninja's_RGR'us

Posted Monday, December 05, 2011 9:40 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:44 PM
Points: 21,376, Visits: 9,585

Reverse the logic

If not exists () Create blank trigger

;
ALTER TRIGGER...

P.S. Please start a new thread next time.

Post #1216435

GilaMonster

Posted Monday, December 05, 2011 9:50 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:31 AM
Points: 38,121, Visits: 30,405

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 »

14 posts, Page 1 of 2

1 2 »»

Permissions