At some point in time, SQL Server DBA’s must have dealt with login failure alerts along with error 18456 and with different states and severity no.
Thanks to the older versions of SQL Server for not presenting enough data in the error log about these login failures and making it a little tricky for new DBA’s.
2013-07-23 00:02:00.34 Logon Error: 18456, Severity: 14, State: 8.
2013-07-23 00:02:00.34 Logon Login failed for user ‘<user name>’. [CLIENT: <ip address>]
By severity, it means the seriousness of the error while state tries to reflect the source of the problem.
Aaron Bertrand has written a very useful blog describing all the states in detail.
Error State Error Description
2 and 5 Invalid userid
6 Attempt to use a Windows login name with SQL Authentication
7 Login disabled and password mismatch
8 Password mismatch
9 Invalid password
11 and 12 Valid login but server access failure
13 SQL Server service paused
16 Suggests that the default database was inaccessible. Main reason could be because the database has been removed, renamed, or is offline (it may be set to Auto Close).
18 Change password required
You may also opt to use SQL Server Profiler trace to decode the exact error and get more details about it (Preferred columns to select) –
– Audit Login Failed (under Security Audit)
– User Error Messages (under Errors & Warnings)
– Error log (under Errors & Warnings)
State 16 means required DB is inaccessible/offline/removed. In this case, which DB is your Login ID trying to access or is there is any agent scheduled job running at the backend which is trying to search for the missing DB or trying to run some SP in that missing DB.
SQL Server Profiler trace will surely help in such cases to identify the problematic jobs.
Hope this helps you to troubleshoot with ease 🙂