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:
EXEC sp_FailedLogins
Example Output:
As we can see above this returns the following information:
Username
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.
SQL Undercover 