Here is a relatively simple yet effective stored procedure to quickly view those failed login attempts (an alternative to viewing the SQL Server Error log manually and filtering).
There are other ways of getting this information, but we liked the idea of having a stored procedure that we could run Ad hoc when we wanted.
Its always nice to have something in your Arsenal that allows you to be proactive and this allows you to proactively check the SQL server error log for failed logins whenever you please, or on a schedule if you use it within an Agent job of course.
So that’s my boring speech out of the way , lets get our hands dirty and show you the procedure:
If we run sp_FailedLogins without any Parameters specified it will return the results for any failed logins that have occured within the last 60 Minutes:
As we can see above this returns the following information:
Number of Attempts by this login
The Last occurrence of a failed login attempt
The Last error produced for the failed login attempt
For searching within a given period we can use the following:
EXEC sp_FailedLogins @FromDate = '20170603 09:00', @ToDate = '20170605 17:00'
We can also Omit the @ToDate if we want to search FROM a date right up to the present time.
EXEC sp_FailedLogins @FromDate = '20170603 09:00'
The procedure has been written to query the current error log file only.
Please be aware that the performance of this procedure will be directly affected by the size of the log and potentially the frequency in which you cycle the log too (depending on log size) , as an example when I run this on our environments it runs in under a second, I would not expect it to take much more than that.
Here is the complete code:
USE [master] GO CREATE PROCEDURE [dbo].[sp_FailedLogins] ( @FromDate DATETIME = NULL, @ToDate DATETIME = NULL ) AS BEGIN --Failed login attempts in the last 60 minutes IF @FromDate IS NULL BEGIN SET @FromDate = DATEADD(MINUTE,-60,GETDATE()) END IF @ToDate IS NULL BEGIN SET @ToDate = GETDATE() END IF OBJECT_ID('Tempdb..#Errors') IS NOT NULL DROP TABLE #Errors CREATE TABLE #Errors ( Logdate Datetime, Processinfo Varchar(30), Text Varchar (255) ) INSERT INTO #Errors EXEC xp_ReadErrorLog 0, 1, N'FAILED',N'login',@FromDate,@ToDate; SELECT REPLACE(LoginErrors.Username,'''','') AS Username, CAST(LoginErrors.Attempts AS NVARCHAR(6)) AS Attempts, LatestDate.Logdate, Latestdate.LastError from ( Select SUBSTRING(text,Patindex('%''%''%',Text),charindex('.',Text)-(Patindex('%''%''%',Text))) as Username,Count(*) AS Attempts From #Errors Errors GROUP BY SUBSTRING(text,Patindex('%''%''%',Text),charindex('.',Text)-(Patindex('%''%''%',Text))) ) LoginErrors CROSS APPLY (SELECT TOP 1 Logdate,text as LastError FROM #Errors LatestDate where LoginErrors.Username = SUBSTRING(text,Patindex('%''%''%',Text),charindex('.',Text)-(Patindex('%''%''%',Text))) ORDER by Logdate DESC) LatestDate Order by LoginErrors.Attempts DESC END GO
This is part of the Undercover Toolbox, a set of useful scripts, procedures and functions for the discerning DBA.