Check Failed Login for SQL 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Check Failed Login for SQL 2005

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2008-02-20 : 23:03:11

We have a similar like this in SQL2000 which is basically check the failed login. Do you know any similar scripts like this? The EXEC master..xp_readerrorlog is only returning some fields.

ALTER PROCEDURE [dbo].[usp_CheckFailedLogins]
(
@IntervalMinutes int = 15
)
AS

SET NOCOUNT ON
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

DECLARE @msg varchar(400)
SELECT @msg = ''

CREATE TABLE #tmpEvent (
EventTime datetime,
EventType varchar(30),
EventText varchar(255),
EventUser varchar(80),
EventFlag bit
)

CREATE TABLE #tmpLog (
StdOutput nvarchar(4000),
ContinuationRow int
)

INSERT INTO #tmpLog
EXEC master..xp_readerrorlog

-- Report events
INSERT INTO #tmpEvent (
EventTime,
EventType,
EventText,
EventUser,
EventFlag
)
SELECT EventTime = CONVERT(datetime,SUBSTRING(StdOutput,1,23),121),
EventType = SUBSTRING(StdOutput,24,10),
EventText = SUBSTRING(StdOutput,34,4000),
EventUser = SUBSTRING(StdOutput,57,LEN(stdOutput)-57-1),
EventFlag = 0
FROM #tmpLog
WHERE SUBSTRING(StdOutput,24,5) = 'logon'
AND ISDATE(SUBSTRING(StdOutput,1,23)) = 1
AND SUBSTRING(StdOutput,34,21) = 'Login failed for user'
AND CONVERT(datetime,SUBSTRING(StdOutput,1,23),121) > DATEADD(mi,-1 * @IntervalMinutes,GetDate())

UPDATE tmpEvent
SET EventFlag = 1
FROM #tmpEvent tmpEvent
WHERE tmpEvent.EventUser IN ( SELECT s.name
FROM master..syslogins s
WHERE s.sysadmin = 1
OR s.securityadmin = 1
OR s.serveradmin = 1
)

IF EXISTS (SELECT EventFlag FROM #tmpEvent WHERE EventFlag = 1)
BEGIN

SELECT @msg = ISNULL(@@SERVERNAME,'') +', ' + CONVERT(varchar(30),GetDate(),121) + ', ' + 'Failed login attempt related to administrator level account. Check the SQL Server Event Log.' + SPACE(1)
SELECT @msg = @msg + 'Login failed for user ''' + EventUser + ''' at ' + CONVERT(varchar(30),MAX(EventTime),121) + '; '
FROM #tmpEvent
WHERE EventFlag = 1
GROUP BY EventUser

-- Raise the error
SELECT @msg = CONVERT(varchar(16),GetDate(),121) + ' - ' + ISNULL(OBJECT_NAME(@@PROCID),'') + ', - ' + @msg
RAISERROR('%s (%d)', 16, 1, @msg, -101)
END

-- Cleanup
DROP TABLE #tmpLog
DROP TABLE #tmpEvent

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-21 : 15:05:56

Similar, you can try it in sql2k5.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources