Re: LOGON trigger

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?


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: application roles
    ... I 've made a trigger on a SQL2000 database to test your solution with the ... But my login-account and database user appear in my logtable instead of the ... if you are on SQL 2005. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)
  • Re: Update trigger
    ... nvltrigger for each column for example. ... SQL> drop trigger emp_upd_trig ... SQL> create table emp ( ... Commit complete. ...
    (comp.databases.oracle.server)
  • Re: Transaction Abort in a trigger SQL 2008, writing to event log
    ... creating another transaction within the trigger. ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.programming)
  • Re: Update trigger
    ... nvltrigger for each column for example. ... record update even if we are only effectively changing one column ... SQL> create or replace trigger emp_upd_trig ... SQL> update emp ...
    (comp.databases.oracle.server)
  • Re: One more trigger question
    ... They also have a history table which shows all the opt outs and opt ... So I figured a trigger on the ... SQL> create table inactive( ... a customer opts out of some email product. ...
    (comp.databases.oracle.misc)