Would like help with LOGON Trigger

Question

I've created a logon trigger in MS SQL that needs to check dm_exec_sessions for a login. This login is the user listed in the connection string and has owner right to the database. If the login is verified, I need it to update a specific table and send an email.

So far I've done just the following piece and it disabled my web site. The error I get is: "Logon failed for login 'dev' due to trigger execution. Changed database context to 'mydatabase'. Changed language setting to us_english."

Any idea what I did wrong? Thanks,

Risho

CREATE TRIGGER TDY_Assets_Notification
ON ALL SERVER WITH EXECUTE AS 'dev'
FOR LOGON
AS
BEGIN

IF ORIGINAL_LOGIN()='dev' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'dev') > 1

     UPDATE Assets_TDY 
        SET Suspense = 1, Warning = 1
        WHERE     (Date_Returned IS NULL) AND (GETDATE() >= DATEADD(day, 3, Date_Return))

END

Answer 1

Try connecting with the Dedicated Administrator Connection. That will give you an opportunity to disable the faulty logon trigger.

DISABLE TRIGGER [TDY_Assets_Notification] ON ALL SERVER