This script is useful for security audits (helping you gather information about failed login attempts), and for checking recent activity by a particular login (before you delete a login, for example).
The script lists all failed login attempts by a user, including the IP address of the computer from which the login attempts were made. The last successful login for a user is also listed.
Note that security logging must be enabled on the SQL Server you are monitoring, otherwise this script will not return accurate results.
LogDate DATETIME,ProcessInfo NVARCHAR(50),ArchiveNumber INT,LogDate DATETIME,LogSize INT)EXEC sp_readerrorlog @lCWHERE ArchiveNumber > @lC--Failed login counts. Useful for security audits.SELECT 'Failed - ' + CONVERT(nvarchar(5), COUNT(Text)) + ' attempts' AS [Login Attempt], Text AS DetailsFROM #TempLogwhere ProcessInfo = 'Logon'--Find Last Successful login. Useful to know before deleting "obsolete" accounts.SELECT Distinct 'Successful - Last login at (' + CONVERT(nvarchar(64), MAX(LogDate)) + ')' AS [Login Attempt], Text AS DetailsFROM #TempLog