2009-09-03

windows user cannot access sql server problem

Problem:
We are running SQL Server 2005 on a Windows 2008 server and I have a situation where some of my users are unable to connect to the SQL Server unless they are a member of the sysadmin group.
Any attempts by these users to login result in a login failed,
Error: 18456, Severity: 14, State: 11
Which indicates that it is a valid user who does not have access to this SQL Server.

Solution 1:
Add user to local admin group, then use "run as administrator" to start SQL management studio. Use can access sql server database.

solution 2:
Enabling the TSQL TCP endpoint for the 'public' role fixed the problem.SQL management studio -> server properties-> permission->public->enable "connectSQL"

Reference reading
Users are unable to connect to SQL Server 2005 unless they are in the sysadmin group
http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/309236fa-d86d-4294-a156-729e2c28b63f

Understanding "login failed" (Error 18456) error messages in SQL Server 2005
http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

Troubleshooting: Login Failed for User 'x'
http://msdn.microsoft.com/en-us/library/ms366351.aspx

No comments: