Personally, I like to keep a close eye on my servers and users/customers. However, not like to do everything manually. This is not only very time consuming, but also most likely to find important issues rather late, hence the script below.
Monitoring failed SQL Server login requests – The script
One of the many things I would like to get notified about is the amount of failed login attempts. I’m not really looking for malicious login attempts, but more or less focus on application accounts, preventing the business from functioning efficiently.
Note: In large organizations you might want to document some basic information about each database including; associated applications, application owner, technical contact person, contact details of a “power user”, SLA details, and the business impact of database/application downtime.
Connect to the SQL Server Instance.
Retrieving the list of failed attempts
And the statement below groups the results logged today but only when exceeding 5 failed attempts. Note: If you don’t want to be bothered by previous results it’s required to keep track of some state information (last execution date or just the previous dataset)
|7||Login failed for user ‘aaaa’. Reason: Could not find a login matching the name p…|
At this point you can push the results to the notification system of your liking.