365DBA Blog

Database Stuff

SQL Server Monitoring Scripts - 3 - Login Failures

In this script, I gather information on failed login attempts so that if there are any users trying to access data which they are not authorized for or if there is a default database in a connection string that might not exist, etc. we can address the issue. You can change the @LastCheck variable to whatever interval you run the checks.

Set NoCount On
Set ANSI_Warnings Off
Select '---------------Login Failure Check---------------'
Declare @LastCheck DateTime
Select @LastCheck = DateAdd(dd, -2, GetDate())
If (@LastCheck Is Null)
Begin
Set @LastCheck = '1-1-2000'
End
If Object_ID('tempdb.dbo.#ErrLog1') Is Not Null
Drop Table #ErrLog1;
Create Table #ErrLog1 (LogDate DateTime, ProcessInfo VarChar(50), LogText VarChar(2000))
Insert Into #ErrLog1 (LogDate, ProcessInfo, LogText) 
Exec xp_readerrorlog 0, 1, N'Login', N'failed'
Delete From #ErrLog1 Where LogDate <= @LastCheck
Select Cast(LogDate As VarChar(25)), Left(LogText, 500) From #ErrLog1
If Object_ID('tempdb.dbo.#ErrLog1') Is Not Null
Drop Table #ErrLog1;
Loading