Cool Tips‎ > ‎SQL Server Security‎ > ‎

Get List of Failed Login Attempts


                 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


Author: ChillyDBA
Date:    9 Jun 2020


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


DECLARE @TSQL NVARCHAR(2000) DECLARE @lC INT CREATE TABLE #TempLog ( LogDate DATETIME, ProcessInfo NVARCHAR(50), [Text] NVARCHAR(MAX)) CREATE TABLE #logF ( ArchiveNumber INT, LogDate DATETIME, LogSize INT ) INSERT INTO #logF EXEC sp_enumerrorlogs SELECT @lC = MIN(ArchiveNumber) FROM #logF WHILE @lC IS NOT NULL BEGIN INSERT INTO #TempLog EXEC sp_readerrorlog @lC SELECT @lC = MIN(ArchiveNumber) FROM #logF WHERE ArchiveNumber > @lC END --Failed login counts. Useful for security audits. SELECT Text,COUNT(Text) Number_Of_Attempts FROM #TempLog where Text 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,Text FROM #TempLog where ProcessInfo = 'LOGON'and Text like '%SUCCEEDED%' and Text not like '%NT AUTHORITY%' Group by Text DROP TABLE #TempLog DROP TABLE #logF

Andy Hughes,
Jun 9, 2020, 5:35 PM