The SS error log indicates the following at a frequency of 2 to 3 times per hour:

Logon failed for login 'NT AUTHORITY\NETWORK SERVICE' due to trigger execution. [CLIENT: ]

There is no logon trigger. How do I discover what trigger is causing the problem since the error log does not say?

(This is on a Win 7 Pro computer running SS 2008r2 developer.)

  • 2
    Are you 100% sure there is no logon trigger? Anything at all in sys.server_triggers? Jan 20 '12 at 13:27
  • select * from sys.server_triggers; returns an empty table. And nothing is shown in the object explorer server objects -> triggers
    – Karl
    Jan 20 '12 at 13:35
  • Are you a sysadmin on the instance so you would definitely see all objects? Jan 20 '12 at 13:36
  • Well, I'm not 100% sure. I connected with 'SA' account (SS authentication of course). Does this account automatically get sysadmin privilages?
    – Karl
    Jan 20 '12 at 13:40
  • 1
    Does the error log mention the state? Seems pretty clear that it's a logon trigger (though I don't recall the exact error that's raised if the classifier function for Resource Governor fails). You may not see the logon trigger in sys.server_triggers due to permissions. On a system with logon triggers in place, it doesn't seem preposterous that those triggers would be protected by whoever created them from whoever might be trying to gain access to the system. Jan 20 '12 at 22:52

Have you considered using a trace to audit logins and/or triggers.

in case you find a trigger using this particular login the following post - Login failed due to trigger execution underlines the security context (see Microsoft answers) in which logon triggers are executed.

Hope this helps.

  • Can you elaborate on why you think Karl might find the post helpful? Please consider summarizing the main point of the post in your answer? Jan 24 '12 at 15:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.