[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQLServer 2008 trigger and login

Posted on 2011-10-11
3
Medium Priority
?
258 Views
Last Modified: 2012-05-12
Hello, I have issue the following command and would like to drop the login_test but I cannot drop it because of the trigger which has been created, where or how do I find this trigger to drop it first.

USE master;
GO
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,
    CHECK_EXPIRATION = ON;
GO

GRANT VIEW SERVER STATE TO login_test;
GO

CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'login_test') > 3
    ROLLBACK;
END;

0
Comment
Question by:vera2006
  • 2
3 Comments
 
LVL 9

Accepted Solution

by:
rajeevnandanmishra earned 1336 total points
ID: 36948555
In the Server Objects -->Triggers
0
 
LVL 9

Assisted Solution

by:rajeevnandanmishra
rajeevnandanmishra earned 1336 total points
ID: 36948564
You can also use the drop trigger statement like,
DROP TRIGGER [connection_limit_trigger] ON ALL SERVER
0
 
LVL 3

Assisted Solution

by:BartVx
BartVx earned 664 total points
ID: 36948589
They can be found in the sys.triggers view of the master database.

Check following links for more info:

How to drop a DDL trigger:

http://msdn.microsoft.com/en-us/library/ms173497.aspx

0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question