Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Microsoft SQL Server: Setup and Administration FAQ

User Management

How to audit the failed logons? by maswien
Posted: 26 Mar 05

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
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.  

Back to Microsoft SQL Server: Setup and Administration FAQ Index
Back to Microsoft SQL Server: Setup and Administration Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close