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: on delete set null
    ... > Nun versuche ich das ganze mit einem Trigger zu simulieren (auf SProcs ... SQL Server verlangt ... > CREATE TABLE voranstellen kann, in denen die Tabellen erzeugt werden, ...
    (microsoft.public.de.sqlserver)
  • Re: CLR-Trigger und temp tables
    ... Meine Frage basiert auf dem Community-Webcast zum SQL Server 2005, ... Trigger nicht installieren kann. ... Ein kleines CLR Trigger Beispiel, dass ich mal in Beta Zeiten verwendet ...
    (microsoft.public.de.sqlserver)
  • Re: capture and save running sql query to database table (for logging)
    ... In SQL Server 2005 you can get the current statement with this query: ... running this inside a trigger returns the SQL statement to create the trigger. ...
    (comp.databases.ms-sqlserver)
  • Re: write conflict error
    ... remote programming and training ... I was hoping to avoid adding the code to all of the forms that use that table as the record source by using the trigger on the table. ... The correct data goes into the other two tables and no errors are recieved which makes me think that it is something that is happening in Access when interfaced with SQL database. ... (tbllineID, tbllinestatusID, actiondate, parts, labor, misc, cstpart, dlrpart, comments, tblacesID) ...
    (microsoft.public.access.modulesdaovba)