Procedure to Return Previous Week’s Failed Login List in SQL Server

Hello everyone,

In this article, I will provide information about the procedure that brings the previous week’s list of failed logins in SQL Server.

It is also very important for security to track failed login attempts to SQL Server.

With the help of the procedure below, you can also track failed logins.

CREATE PROC sp_PreviousWeekFailedLoginList 
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ErrorLogCount INT;
    DECLARE @LastLogDate DATETIME;

    DECLARE @ErrorLogInfo TABLE
    (
        LogDate DATETIME,
        ProcessInfo NVARCHAR(50),
        [Text] NVARCHAR(MAX)
    );

    DECLARE @EnumErrorLogs TABLE
    (
        [Archive#] INT,
        [Date] DATETIME,
        LogFileSizeMB INT
    );

    INSERT INTO @EnumErrorLogs
    EXEC sp_enumerrorlogs;

    SELECT @ErrorLogCount = MIN([Archive#]),
           @LastLogDate = MAX([Date])
    FROM @EnumErrorLogs;

    WHILE @ErrorLogCount IS NOT NULL
    BEGIN

        INSERT INTO @ErrorLogInfo
        EXEC sp_readerrorlog @ErrorLogCount;

        SELECT @ErrorLogCount = MIN([Archive#]),
               @LastLogDate = MAX([Date])
        FROM @EnumErrorLogs
        WHERE [Archive#] > @ErrorLogCount
              AND @LastLogDate > GETDATE() - 1;

    END;

    SELECT COUNT(Text) AS NumberOfAttempts,
           Text AS Details,
           MIN(LogDate) AS MinLogDate,
           MAX(LogDate) AS MaxLogDate
    FROM @ErrorLogInfo
    WHERE ProcessInfo = 'Logon'
          AND Text LIKE '%fail%'
          AND LogDate > GETDATE() - 1
    GROUP BY Text
    ORDER BY NumberOfAttempts DESC;

    SET NOCOUNT OFF;
END;


--Use of Procedure

EXEC sp_PreviousWeekFailedLoginList ;

Good luck to everyone in business and life.