windows user cannot access sql server 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

Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Troubleshooting: Login Failed for User 'x'

No comments: