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.
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;
User contributions licensed under CC BY-SA 3.0