Local user login access in sql server 2008
To figure out exactly what is happening, you should check the State that is reported in the message, either in the event log or SQL Server log. For example:


  Error: 18456, Severity: 14, State: 8.


The state will usually help narrow down the exact cause. Here is a list of the states and their meanings:

Err  Message
 2   Invalid userid 
 5   Invalid userid
 6   Attempt to use a Windows login name with SQL Authentication
 7   Login disabled or password mismatch
 8   Password mismatch
 9   Invalid password
11   Valid login but server access failure
12   Valid login but server access failure
13   SQL Server service paused
18   Change password required