Create job - Alert-LoginFailureDetected

This job executes the stored procedure 'usp_AlertLoginFailed' when the alert is triggered. After calling the stored procedure, the variable @SendAlert returns the value 'YES' when an e-mail needs to be sent.

Job Name: Alert-LoginFailureDetected--

DECLARE @SendAlert char(3)

DECLARE @iTimeRangeInSeconds int

SET @iTimeRangeInSeconds = 3600

-- Get the last time the alert message was sent

DECLARE @dtLastRun datetime

SELECT top 1 @dtLastRun =

CAST(CAST(run_date AS char(8)) + ' ' + -- Convert run_date to DateTime data type

STUFF(STUFF( -- Insert : into Time

RIGHT('000000' + -- Add leading Zeros

CAST(run_time AS varchar(6)) ,6) , 3, 0, ':'), 6, 0, ':') AS datetime)

FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobhistory B

WHERE A.job_id = B.job_id AND B.run_status = 1

AND A.name = 'Alert-LoginFailureDetected-Send-Alert'

ORDER BY 1 DESC

-- Check if the last alert sent was within the specified time frame

SELECT 'Job Last Run Date', @dtLastRun

IF @dtLastRun is null OR

dateadd(s, -@iTimeRangeInSeconds, current_timestamp ) > @dtLastRun

BEGIN

EXEC admin.dbo.usp_AlertLoginFailed

@iFailureCount=3,

@iTimeRangeInSeconds=@iTimeRangeInSeconds ,

@SendAlert=@SendAlert OUTPUT

SELECT '*** SendAlert=' + @SendAlert

IF @SendAlert='YES'

BEGIN

EXEC msdb.dbo.sp_start_job N'Alert-LoginFailureDetected-Send-Alert'

WAITFOR DELAY '00:00:15' -- Give Send Alert a chance to finish

END

END

ELSE

BEGIN

SELECT '*** Alert Message recently sent. No more checks will be made until ',

dateadd(s, @iTimeRangeInSeconds, @dtLastRun )

END

Parameters:

* @iFailureCount - Send e-mail when this number of login failures occurred within the specified time range.

* @iTimeRangeInSeconds - It has two uses. First, it specifies how many seconds in the past to check for login failures. Second, after sending an e-mail, update the alert setting "delay between responses" to this value. The alert will then wait for the specified number of seconds before responding to additional login failures.

* @EmailRecipients - List each e-mail address that will receive the message and separate each address by a comma.

* @SendAlert - This variable returns the value "YES" after sending an e-mail. Use this variable to execute other stored procedures or jobs in response to repeated login failures.

Create Job - Alert-LoginFailureDetected-Send-Alert --

This job is started by the job 'Alert-LoginFailureDetected' when an e-mail needs to be sent to the DBA team alerting them of repeated login failures. The SQL contains the e-mail stored procedures used for both SQL Server 2000 and 2005. Delete the one you do not need. Set the variable @EmailRecipients to your DBA teams e-mail address.

PRINT ' High number of failed login alerts detected'

DECLARE @EmailRecipients varchar(255)

DECLARE @vcSubject varchar(255)

DECLARE @vcMessage varchar(255)

SET @EmailRecipients = 'myteam@yourdomain.com'

SET @vcSubject = @@ServerName + ': High Volume of login Failure Alerts Detected'

SET @vcMessage = 'Please check the event logs for the user login that attempted to login.'

PRINT 'Alert E-mail sent '

-- Stored procedure used in SQL Server 2005

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Default',

@recipients = @EmailRecipients,

@subject = @vcSubject,

@Body =@vcMessage

-- Stored procedure used in SQL Server 2000

EXEC admin.dbo.usp_cdosendmail

@From =@@SERVERNAME,

@To =@EmailRecipients,

@Subject =@vcSubject,

@Body =@vcMessage

Hope this helps...