DROP TRIGGER with ON ALL SERVER

0

I have a trigger to prevent someone using Management Studio:

CREATE TRIGGER [TR_LOGON_APP]
ON ALL SERVER 
FOR LOGON
AS
BEGIN
   DECLARE @program_name NVARCHAR(128)
   DECLARE @host_name NVARCHAR(128)

   SELECT 
       @program_name = program_name, 
       @host_name = host_name
   FROM 
       sys.dm_exec_sessions AS c
   WHERE 
       c.session_id = @@spid

   IF @program_name LIKE '%Management%Studio%' 
   BEGIN
       RAISERROR('Access Denied.',16,1)
       ROLLBACK;
   END
END;

How to check that if it already exists, the trigger will be dropped first?

I have tried using :

IF EXISTS (SELECT * FROM sys.objects WHERE [name] = N'TR_LOGON_APP' AND [type] = 'TR')
BEGIN
      DROP TRIGGER [TR_LOGON_APP];
END;

and

IF EXISTS (SELECT * FROM sys.objects WHERE [name] = N'TR_LOGON_APP' AND [type] = 'TR')
BEGIN
      DROP TRIGGER [TR_LOGON_APP] ON ALL SERVER;
END;

But both return an error :

System.Data.OleDb.OleDbException (0x80040E14): There is already an object named 'TR_LOGON_APP' in the database.

sql-server
asked on Stack Overflow Dec 8, 2017 by Jun Rikson • edited Dec 8, 2017 by marc_s

1 Answer

2

The sys.objects catalog view contains database objects, not server-level objects. Use sys.server_triggers instead:

IF EXISTS(SELECT 1 FROM sys.server_triggers WHERE name = N'TR_LOGON_APP')
BEGIN
    DROP TRIGGER TR_LOGON_APP ON ALL SERVER;
END;
answered on Stack Overflow Dec 8, 2017 by Dan Guzman

User contributions licensed under CC BY-SA 3.0