How To Send Email For Failed Logons

As a Database Administrator, we need to follow Failed Logons.

And we need to warn users for unauthorized access attempts.

Or if there is a problem, we need to solve the problem.

You may want to check out the article titled “SQL Server Account Lockout Policy“.

We will create 2 alerts to send mail to Logon’s who have failed.

To create the first one, right-click on Alerts under SQL Server Agent and select New Alert

In the General tab we select ErrorNumber as below and write 18456 and we give a name from Name.

Then we go to the Response tab and select an operator by selecting Notify operators.

You may want to read the article “How To Create An Operator On SQL Server and What is It“.

Finally, go to the Options tab and select Email from Include alert error text in: and press OK to define the first alert.

We do the same for 18487 ErrorNumber to create another alert.

You can read the article titled “How To Configure Database Mail On SQL Server” to send mail from your server.

You can find more detailed information about alerts in my articles “Always On Availability Group Alert System” and “SQL Server Best Practices and Configurations that need to be made After Installation“.