DDL Trigger For Logon Insert data to a table

Question

I have a below DDL Trigger on my server

CREATE TRIGGER [DDLForLogin] ON ALL SERVER
FOR LOGON 
AS BEGIN
    DECLARE @data XML
    SET @data = EVENTDATA()

    IF EXISTS(SELECT * FROM sys.Databases WHERE NAME = 'DatabaseMaintenance') Begin
        INSERT INTO DatabaseMaintenance.dbo.MyTable (UserName, HostName, ApplicationName, EventDataValue)
        VALUES (CURRENT_USER, HOST_Name(), APP_NAME(),@Data)
    END  
END;

a login as Windows Authentication insert one row on MyTable but a login as SQL Server Authentication raised below error :

Logon failed for login 'xxx' due to trigger execution.
Changed database context to 'master'.
Changed language setting to us_english. (Microsofr SQL Server, Error: 17892)

EDIT

I GRANT INSERT on my table to PUBLIC.

but no change on raised error.

EDIT2

I change my trigger and add With Execute AS 'sa' on the trigger

but no change on raised error.

Answer 1

Try adding an appropriate EXECUTE AS clause to your trigger - the default is CALLER, so unless that user has permissions to insert into your audit table¹, the trigger will fail.

Also, then use ORIGINAL_LOGIN() in the trigger to get the correct login information

¹ Which you normally don't want - because otherwise every user can forge entries alleging that other users have logged in.

Answer 2

Must change trigger to below code:

CREATE TRIGGER [DDLForLogin] ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON 
AS BEGIN
    DECLARE @data XML
    SET @data = EVENTDATA()

    DECLARE @IsPooled int
    SET @IsPooled = @data.value('(/EVENT_INSTANCE/IsPooled)[1]', 'int')

    IF EXISTS(SELECT * FROM sys.Databases WHERE NAME = 'DatabaseMaintenance')AND (@IsPooled=0) Begin
        insert into DatabaseMaintenance.dbo.Login (UserName, HostName, ApplicationName, EventDataValue)
        values (ORIGINAL_LOGIN(), HOST_Name(), APP_NAME(),@Data)
    END
END;