Logon trigger error inserting in char field with '$' at the end

  • Hello. I created a login trigger to insert data for each login in a table, and it works for all logins except one that is format domain\login and the login ends with the dollar sign(actual name is domain\CTXDEVDCSI1$).

    I had been using varchar, but after reading other forum posts, I changed the varchar's to nvarchar's, but it still fails for that id.

    The errors written to the sql server error log were the usual "login failed due to trigger execution".

    Any suggestions would be much appreciated!

    Here's the modified table ddl:

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[rvvlogindata](

    [sessionId] [int] NULL,

    [LoginTime] [datetime] NULL,

    [HostName] [nvarchar](50) NULL,

    [ProgramName] [nvarchar](300) NULL,

    [LoginName] [nvarchar](50) NULL,

    [ClientHost] [nvarchar](50) NULL

    ) ON [PRIMARY]

    GO

    Here's the logon trigger code:

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    create trigger [LOGIN_IP_RESTRICTION] on all server for logon

    as

    Begin

    Declare @LogonTriggerData xml,

    @EventTime datetime,

    @LoginName nvarchar(50),

    @ClientHost nvarchar(50),

    @HostName nvarchar(50),

    @AppName nvarchar(300)

    Set @LogonTriggerData = eventdata()

    set @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')

    set @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')

    set @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')

    set @HostName = HOST_NAME()

    set @AppName = APP_NAME()

    insert into dsa.dbo.rvvlogindata

    (

    sessionId,

    LoginTime,

    HostName,

    ProgramName,

    LoginName,

    ClientHost

    )

    select @@spid,

    @EventTime,

    convert(nvarchar(50),@HostName),

    convert(nvarchar(300),@AppName),

    convert(nvarchar(50),@LoginName),

    convert(nvarchar(50),@ClientHost)

    END

    GO

    SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    ENABLE TRIGGER [LOGIN_IP_RESTRICTION] ON ALL SERVER

    GO

  • if you created a table that captures login info, then i think you need to GRANT INSERT ON [dbo].[rvvlogindata] TO PUBLIC so that any login would not fail inside the trigger.

    i'd bet you a donut that login you mentioned doesn't have insert permissions to the table, and thus fails inside the trigger.

    the error log would have the details, and it would probably say object [dbo].[rvvlogindata]( does not exist or you don't have permission to perform this action for the offending user.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks for the suggestion, but I had already granted insert to public, which is why all the logins can login but the one with special character in the name(possibly due to the $ being the last character?)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply