Lists user login attempts to a SQL Server (failed and successful login attempts). This script is useful for security audits (helping you gather information about failed login attempts), and for checking recent activity by a particular login (before you delete a login, for example).
-- Lists user login attempts to a SQL Server (failed and successful login
-- attempts)
--
-- This script is useful for security audits (helping you gather information
-- about failed login attempts), and for checking recent activity by a
-- particular login (before you delete a login, for example).
--
-- The script lists all failed login attempts by a user, including the IP
-- address of the computer from which the login attempts were made. The last
-- successful login for a user is also listed.
--
-- Note: security logging must be enabled on the SQL Server you are monitoring,
-- otherwise this script will not return accurate results.
declare @TSQL nvarchar(2000);
declare @lC int;
create table #TempLog(
LogDate datetime,
ProcessInfo nvarchar(50),
Text nvarchar(max)
);
create table #logF(
ArchiveNumber int,
LogDate datetime,
LogSize int
);
insert into #logF exec sp_enumerrorlogs;
select @lC = min(ArchiveNumber) from #logF;
while @lC is not null
begin
insert into #TempLog exec sp_readerrorlog @lC;
select @lC = min(ArchiveNumber) from #logF where ArchiveNumber > @lC;
end;
--Failed login counts. Useful for security audits.
select
'Failed - ' + convert(nvarchar(5), count(Text)) + ' attempts' as [Login Attempt],
Text as Details
from #TempLog
where ProcessInfo = 'Logon'
and Text like '%failed%'
group by Text;
--Find Last Successful login. Useful to know before deleting "obsolete" accounts.
select distinct 'Successful - Last login at (' + convert(nvarchar(64), max(LogDate)) + ')' as [Login Attempt],
Text as Details
from #TempLog
where ProcessInfo = 'Logon'
and Text like '%succeeded%'
and Text not like '%NT AUTHORITY%'
group by Text;
drop table #TempLog;
drop table #logF;