How to audit the failed logons?

If the audit level is set to 'failure', the failed logons are recorded in SQL Server error logs. The drawback is that there are too many other events are logged we are not interested here. To get an audit report weekly to show which accounts have failed logons, we need to use procedure xp_readerrorlog and create a scheduled job for it.

-- create a table in master database to hold the
-- events in error log

CREATE TABLE utbl_errorlog (
rowID int IDENTITY,
textRow varchar(4000),
continuationRow int
)

-- procedure for populate the table

create procedure usp_audit_logins
as
set nocount on
-- temporary table
CREATE TABLE #errorlog (
rowID int IDENTITY,
textRow varchar(4000),
continuationRow int
)
-- import the error log
INSERT INTO #errorlog
(textRow, continuationRow)
EXEC master.dbo.xp_readerrorlog

-- ignore the duplicate records
insert into utbl_errorlog
SELECT textRow
FROM #errorlog
WHERE CHARINDEX('login failed', textRow) > 0
and textRow not in (select textRow from utbl_errorlog)
ORDER by rowID

DROP TABLE #errorlog

-- truncate the error log
EXEC sp_cycle_errorlog
set nocount off


After create above table and procedure, create a job to run this store procedure weekly, you will get a weekly user auditing report.