Report Login Failures

It sometimes become very critical in some environments that there are a huge login failures and needs to be reported to take appropriate action. To accommodate this type of situations, I have developed a stored proc which gives a report of Failed Login attempts in a specified Time Frame.
This SP expects a  parameter @TimeInMin which has a default value of 15 Minutes.

Create Proc ReportLoginFailure (@TimeinMin int =15)

As

/*

Author            :Sandesh Segu

Purpose           :To report the failed loing attempts if any in given 

                   range of time.

Version           :1.0

More Scripts      :http://sanssql.blogspot.com

*/

Declare @Count int

Create Table #ErrorLog

(ErrorLoggedDate DateTime,

ProcessInfo nvarchar(50),

ErrorText nvarchar(1000))

Insert into #ErrorLog exec sp_readerrorlog

Select @Count=Count(*) from #ErrorLog where ErrorText like 'Login failed for user%'

and datediff(Minute,ErrorLoggedDate,getdate())<=@TimeinMin

If @Count>0

      Begin

            Select Report=Cast(@Count As Varchar)+' Login Failures Reported in past '+Cast (@TimeinMin As varchar)+' Minutes.'

            Select * from #ErrorLog where ErrorText like 'Login failed for user%'

            and datediff(Minute,ErrorLoggedDate,getdate())<=@TimeinMin

      End

Else

      Select Report='No Login Failures Reported in the Specified Time Frame.'

Drop table #ErrorLog

GO

/*

Usage: Specify the time limit parameter in minutes for the SP. Default is 15 mins.

Exec ReportLoginFailure 600

*/

When there are no failures reported then the output will be:

When there are failures reported then the output will be:

SQL Server 2008 Certification path

For those who are interested in SQL Server 2008 Certification, download the pdf from the below link. This reference pdf is self explanatory and gives the complete list of certifications  that needs to be completed by an individual to gain MCTS and MCITP in SQL Server 2008 from Microsoft.

Microsoft SQL Server 2008 Certification Paths

Schema Changes History Report in SQL Server 2008

In one of my previous posts "Find out who has changed what" I had explained how to find out who has changed what in SQL server when the trace is enabled.

In SQL Server 2008, the Default trace is enabled by default during the installation and this default trace in SQL Server 2008 acts as a Blackbox for the SQL Server. Hence it is also called as "Black Box" in SQL Server 2008 and  the default trace cannot be stopped by using the Query "Exec sp_trace_setstatus 'Trace_id',0 --0 stops the trace " . This has to be stopped using the sp_configure.
To Use this report, Just right Click on the database and choose the option "Reports" and Select "Standard Reports" and choose "Schema Changes History".

To Test this, I executed the below query on AdventureWorks Database. I have also included the Statements "Waitfor Delay" to know the Time Difference.

Use AdventureWorks

Go

Create Table Tbl_TestSchemaChangesHistoryReport

( Id int,

Name Varchar(50))

Waitfor Delay '00:01:00' -- to know the time difference

Alter table Tbl_TestSchemaChangesHistoryReport Add Address_Column varchar(100)

Waitfor Delay '00:01:00' -- to know the time difference

Drop table Tbl_TestSchemaChangesHistoryReport 

And here is the results.