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

migrated from stackoverflow.com Jan 20 '12 at 21:17

This question came from our site for professional and enthusiast programmers.

  • 2
    Are you 100% sure there is no logon trigger? Anything at all in sys.server_triggers? – Martin Smith 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? – Martin Smith 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. – Aaron Bertrand 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? – Jack Douglas 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.