One of my colleagues came to me and showed me a strange problem on SQL server. When a user uses Windows Login to connect to SQL server the user get the 18456 error message. But if the user is added to SysAdmin role the user is able to login to the SQL server. My colleague has deleted the window login from the SQL server and added the window login back to the SQL server. It does not resolve the problem.

The SQL Server Logs shows the following message:

SQL Error Log Message:

Date  12/11/2008 9:30:16 AM

Log  SQL Server (Current - 12/11/2008 2:50:00 PM)

Source  Logon

Message

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

 

The followings are the steps that we used to solve this problem:

1.        Check if the login has granted access permission to the default database

2.        Check if the login is not disable

3.        Check if the login is denied database engine access

4.        Run the following script:

Exec  xp_logininfo  'domain\user'

 ( Replace the domain with your domain name and user with the right user name )

If there is no result returning back, then the window login is denied database engine access through windows group.

5.        Run the following script to get a list of window groups that are denied SQL database engine access

select prin.[name], prin.type_desc

from sys.server_principals prin

JOIN sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id

where perm.state_desc = 'DENY'

6.        Make sure that the window login is not a member of the Window Groups in the list.