Re: LOGON trigger

In article <Xns9A5289BAEE0Yazorman@xxxxxxxxx>, esquel@xxxxxxxxxxxxx
says...
Mark Clark (M-Clark-nospam@xxxxxxxxxxxxx) writes:
When I look at the audit logs for the server, I can see the trigger
firing as expected with "Trigger" as the 'LoginName'. However, now for
SQL statements run from inside the particular application, I also see
"Trigger" in 'LoginName'. The 'SessionLoginName' has the correct login
name used by the app, but 'LoginName' shows "Trigger", whereas before
the logon trigger was implemented 'LoginName' showed the same value as
'SessionLoginName'. User "Trigger" has been granted only rights to see
tables in the trigger database. My question is, is that normal behavior
for the login name to carry over from the logon trigger into the actual
application? Is there some way to have 'LoginName' go back to showing
the same user as 'SessionLoginName' instead of "Trigger"? It's just
confusing that the login name used for the trigger is seeming to go
outside the trigger and it makes the audit logs look strange.

I've now looked at this, and I was not able to reproduce the behaviour.
But it occurs to me that I'm not really sure what you mean with "Audit
Logs". Judging from the names, it sounds like you have run a Profiler
trace, and it was in Profiler I looked when I did my test. But maybe
I should have looked somewhere else?


You're right, it's in the Profiler. I look at the trace files in
Profiler, and I see the symptoms I described. The 'LoginName' shows my
trigger user, and 'SessionLoginName' shows the true user that is logged
in. I would expect to see that when the trigger is being executed, but I
don't understand why the trigger user is still being used outside the
scope of the trigger when running SQL statements in the application.
.

Relevant Pages

  • Re: LOGON trigger
    ... firing as expected with "Trigger" as the 'LoginName'. ... SQL statements run from inside the particular application, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)
  • Re: LOGON trigger
    ... ON ALL SERVER WITH EXECUTE AS 'Trigger' ... When I look at the audit logs for the server, ... firing as expected with "Trigger" as the 'LoginName'. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)
  • LOGON trigger
    ... I have a logon trigger similar to the following: ... When I look at the audit logs for the server, ... firing as expected with "Trigger" as the 'LoginName'. ...
    (microsoft.public.sqlserver.security)
  • Re: Auditing changes made to table design (syscolumns table)
    ... With out using profiler the only way I know of is to get this info from the ... Keep in mind I know the database and the tables I would ... I attempted to write a trigger to log when a change was made to the ... > certain criteria against every transaction. ...
    (microsoft.public.sqlserver.security)
  • Re: Updating Subscriber Question
    ... Through profiler I have determined the process is hanging up in the update ... trigger when the sp_MSget_publisher_rpc procedure is called. ... MSsubscription_properties table is queried to get the login id for the ... "Paul Ibison" wrote: ...
    (microsoft.public.sqlserver.replication)