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

Question

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

richard.vanveen

Right there with Babe

Points: 773
More actions
April 10, 2014 at 11:43 am

#284471

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

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

richard.vanveen Right there with Babe Points: 773 More actions · Answer 2

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?)