Error 18456 Login Failure SQL Server

SQL Server will capture all the failed login attempts into error log by default and also we can view the login failures from the event viewer.

Login failed for "sa" Reason: Password did not match that for the login provided [Client : xx.xx.xx.xx]

18546 States
State 8 - Receives when password is wrong for the specified user.

State 5 - Receives when the specified login doesn't exists on the server.

State 1 - Received when the specified login is disabled at server.


Below query displays error codes of the login failures in SQL Server 2008 where we can get the information about those error codes using net helpmsg

SELECT CONVERT (varchar(30), GETDATE(), 121) as Run_Time,
dateadd (ms, (ST.[RecordTime] - sys.ms_ticks), GETDATE()) as [Notification_Time],
ST.* , sys.ms_ticks AS [Current Time]
FROM
(SELECT
RBXML.value('(//Record/Error/ErrorCode)[1]', 'varchar(30)') AS [ErrorCode],
RBXML.value('(//Record/Error/CallingAPIName)[1]', 'varchar(255)') AS [CallingAPIName],
RBXML.value('(//Record/Error/APIName)[1]', 'varchar(255)') AS [APIName],
RBXML.value('(//Record/Error/SPID)[1]', 'int') AS [SPID],
RBXML.value('(//Record/@id)[1]', 'bigint') AS [Record Id],
RBXML.value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
RBXML.value('(//Record/@time)[1]', 'bigint') AS [RecordTime]
FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR') AS RB(RBXML)) ST
CROSS JOIN sys.dm_os_sys_info sys
ORDER BY ST.[RecordTime] ASC
From the output we can see hexadecimal error codes like 0x534 = 1332 (decimal), 0x139F = 5023.
We can get the error message by running the below command from the command prompt

net helpmsg 1332
No mapping between account names and security IDs was done.

net helpmsg 5023
The group or resource is not in the correct state to perform the requested operation.