SQLTeam.com | Weblogs | Forums

Using Server Objects\Triggers


#1

I have a Server Object\Trigger set up (inherited) as a logon trigger to look for not only the user name but the host it is coming from, if the host is incorrect, the connection is rejected.

The issue is that the Trigger magically went poof. So I am wanting to figure out how to not allow or at least notify if this happens again.

I was thinking of a Preventing DDL Action Trigger. however I am not sure since the trigger is in Server Objects not in a database specific.

CREATE TRIGGER TR_StopTriggerDelete
ON ALL SERVER
FOR SERVER_OBJECTS
AS
--EMAIL HERE


#2

[code]create trigger srvTgr_DropTrigger
on ALL SERVER
FOR DROP_TRIGGER
AS
SET NOCOUNT ON;

select 'Hello, world!'
go[/code]


#3
CREATE TRIGGER [Check_Logons_Trigger]
ON ALL SERVER
AFTER LOGON
AS
IF HOST_NAME() NOT IN ('goodhost1','goodhost2','goodhost3')
BEGIN
    DECLARE @host_name nvarchar(128)
    SET @host_name = HOST_NAME()
    RAISERROR('This host name, "%s%", is not allowed to log into this instance; cancelling logon request.', 16, 1, @host_name)
    ROLLBACK TRANSACTION --cancel login
END
IF ORIGINAL_LOGIN() IN ('badlogin1','badlogin2')
BEGIN
    DECLARE @login_name nvarchar(128)
    SET @login_name = ORIGINAL_LOGIN()
    RAISERROR('This login, "%s%", is not allowed to log into this instance; cancelling logon request.', 16, 1, @login_name)
    ROLLBACK TRANSACTION --cancel login
END
RETURN --continue login as normal
GO --end of trigger

#4

I'm not sure my questions was understood correctly. I am not worried about writing the logon trigger, I am trying to find a way to send out a notification if the logon trigger is DROPPED

Sort of Like Stephen's said but it doesn't recognize the trigger because it doesn't look in the right place.


#5

I may not understand the problem (it's Monday morning here) but what about restricting permissions such only one or two people can drop/update the trigger?