Thursday, May 27, 2010

Storing Login Errors

In an effor to post more often, I'm posting something very simple. I record (mainly for auditing reasons) all login failures. Nightly (at 11:59) I store the days errors in a table. This is how I do that:


-- =================================================================
-- Author: Eric Zierdt
-- Create date: 09/24/2009
-- Description: Records daily Login Errors
-- exec DBA_INSERT_LOGIN_AUDIT
-- =================================================================

CREATE PROCEDURE [dbo].[DBA_INSERT_LOGIN_AUDIT]

AS
BEGIN

CREATE TABLE #temp_login_trace
(LogDate DATETIME,
ProcessInfo VARCHAR(100),
[Text] VARCHAR(MAX))

INSERT #temp_login_trace
EXEC xp_readerrorlog 0,1,'Login'

INSERT INTO dbo.DBA_LOGIN_AUDIT
SELECT LogDate
,@@Servername
,ProcessInfo
,[Text]
FROM #temp_login_trace
WHERE LogDate > CAST(GETDATE() AS Date)

DROP TABLE #temp_login_trace

END

No comments:

Post a Comment