Question :
I am setting up alerts in SQL Server and am interested in what events I should be raising alerts for.
In particular, I am looking for any events related to security.
I am already monitoring error 18456.
Answer :
It’s a good idea to create SQL alerts in the SQL agent for all errors of severity 16 through to 25. Also create 1 SQL alert for each of these error IDs 823 824 825.
I would create an operator that points to an AD group (probably DBA or ITsupport) so that a team can be alerted or you can drop users in and out of that group when you are away so that errors are not missed.
This will keep you in the loop when things are happening on your server that you should know about but something like Redgate SQL Monitor is a much better solution.
I would start with this list of errors, most of the login errors I believe are around 18400+. The only other two I think I would add would be:
18452:
Login failed. The login is from an untrusted domain and cannot be used with Windows Authentication.%.*ls
If you are prone to disable logins I would include this as well:
18470:
Login failed for user ‘%.*ls’. Reason: The account is disabled.%.*ls
I would recommend any error that decodes to a SQL Syntax error. #113 (unterminated comment) and #208 (unknown object name) are particularly ominous.