Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
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
show 4 more comments

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

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

1 Answer

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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