SQL tips for database administrator RSS 2.0
# Thursday, 11 December 2008

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.

Thursday, 11 December 2008 23:39:15 UTC  #    Comments [1] -
SQL Security
Archive
<2017 August>
SunMonTueWedThuFriSat
303112345
6789101112
13141516171819
20212223242526
272829303112
3456789
Statistics
Total Posts: 6
This Year: 0
This Month: 0
This Week: 0
Comments: 2
All Content © 2017, Full Time DBA