T-SQL query to find repetitive failed logins in SQL Server
MSSQL, T-SQL Scripts login failed, SQL server, TSQL 0
(0)
Below script is used to find logins which are failing repeatedly since 1 day. you can change the number of days as per your requirement. this script is taken from Here
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
)
CREATE TABLE #FailedLogins
(
Server_Name nVARCHAR(100),
NumberofAttempts int,
Details nVARCHAR(500),
MinLogDate DateTime,
MaxLogDate DateTime
)
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
INSERT INTO #FailedLogins
SELECT @@servername as Server_Name, 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, CAST(LogDate as Date)
ORDER BY NumberOfAttempts DESC;
WITH CTE AS
(
SELECT Server_Name, NumberofAttempts, Details, MinLogDate, MaxLogDate, RANK() OVER (Partition by Details Order BY MinLogdate) RepeatOff
FROM #failedlogins
)
SELECT *
FROM CTE
WHERE RepeatOff = 1
DROP TABLE #failedlogins
SET NOCOUNT OFF
Sample Output:
![](https://dbalyfe.com/wp-content/uploads/2022/09/Screenshot-2022-09-20-003039-1.jpg)
How useful was this post?
Click on a star to rate it!
Average rating 0 / 5. Vote count: 0
No votes so far! Be the first to rate this post.