SQL Server – Read Errorlog

Enable Audit log to capture successful and failed Logins

— To Enable Failed and Successful Logins
— Need to Restart the Server for this to effect

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
 N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 3;
GO

— Query for Successful Logins

 EXEC sys.sp_readerrorlog @p1 = 0, -- int -- 0 = current, 1 = Archive #1, 2 = Archive
 @p2 = 1, -- int -- 1 or NULL = error log, 2 = SQL Agent log
 @p3 = 'succe', -- varchar(255) -- String one we want to search for
 @p4 = 'login'
 -- varchar(255) -- String two we want to search to further refine the result

— Login succeeded for user ‘xxxxxx’. Connection made using Windows authentication. [CLIENT: xx.xx.xx.xx]

— Query for Failed Logins

 EXEC sys.sp_readerrorlog @p1 = 0, -- int -- 0 = current, 1 = Archive #1, 2 = Archive
 @p2 = 1, -- int -- 1 or NULL = error log, 2 = SQL Agent log
 @p3 = 'failed', -- varchar(255) -- String one we want to search for
 @p4 = 'login' -- varchar(255) -- String two we want to search to further refine the result

— Login failed for user ”. Reason: Could not find a login matching the name provided. [CLIENT: ]