Get List of Failed Login Attempts

Applicability:

SQL Server 2000: N/A

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Tested

Credits:

Author: ChillyDBA

Date: 9 Jun 2020

Description

Get a list of fail;ed login attempts - extracted from the server error logs

Code

DECLARE @TSQL NVARCHAR(2000)DECLARE @lC INTCREATE TABLE #TempLog (LogDate DATETIME,ProcessInfo NVARCHAR(50),[Text] NVARCHAR(MAX))CREATE TABLE #logF (ArchiveNumber INT,LogDate DATETIME,LogSize INT)INSERT INTO #logFEXEC sp_enumerrorlogsSELECT @lC = MIN(ArchiveNumber) FROM #logFWHILE @lC IS NOT NULLBEGININSERT INTO #TempLogEXEC sp_readerrorlog @lCSELECT @lC = MIN(ArchiveNumber) FROM #logFWHERE ArchiveNumber > @lCEND--Failed login counts. Useful for security audits. SELECT Text,COUNT(Text) Number_Of_AttemptsFROM #TempLog whereText like '%failed%' and ProcessInfo = 'LOGON'Group by Text--Find Last Successful login. Useful to know before deleting "obsolete" accounts. SELECT Distinct MAX(logdate) last_login,TextFROM #TempLogwhere ProcessInfo = 'LOGON'and Text like '%SUCCEEDED%'and Text not like '%NT AUTHORITY%'Group by TextDROP TABLE #TempLogDROP TABLE #logF