RE: Login Error: 18456, Severity: 14, State:11

The effect is the same. The problem is the Login is denied. The db_datareader
role would be the permissions on the database. The problem is the server is
denying access.
Here is the log of my test:
(This was from a cmd shell running as thoughts\LimitedUser)
C:\Program Files\Microsoft SQL Server\90\Tools\Bin>sqlcmd -S COUGAR\SQLEXPRESS
Msg 18456, Level 14, State 1, Server COUGAR\SQLEXPRESS, Line 1
Login failed for user 'thoughts\LimitedUser'.

C:\Program Files\Microsoft SQL Server\90\Tools\Bin>

The SQL server error log shows the lines:

05/31/2007 08:00:48,Logon,Unknown,Login failed for user
'thoughts\LimitedUser'. [CLIENT: <local machine>]
05/31/2007 08:00:48,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 11.

The ONLY permission that permits login is the 'Control Server' permission.
Note that I tried turning on ALL the server permissions EXCEPT 'Control
Server' and the login was still denied.


"Charles Wang[MSFT]" wrote:

Hi,
I understand that you could not locally connect to your SQL EXPRESS server
unless you grant "Control Server" permission to the domain login account.
If I have misunderstood, please let me know.

I think that your connection string is no problem. What is the result if
you explicitly add the database role db_datareader to the user?

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications

If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.


Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================


.

Relevant Pages

  • Re: GRANT CREATE DATABASE versus GRANT ALTER ANY LOGIN
    ... server, and is a member of the sysadmin server role. ... could not grant "alter any login" permission to a domain login. ... server or it has been removed "sysadmin" server role. ... Microsoft Online Community Support ...
    (microsoft.public.sqlserver.security)
  • Re: Allowing Anonymous write access only.
    ... need at least READ permission for login. ... > been set up so that anonymous FTP users have write access only, this> may seem insecure and we do get a certain ammount of hackers or> taggers testing the system by dropping test files and folders onto the> server, but because anonymous users do not have read access they soon> find that they cannot download anything they upload and go elsewhere. ... This is where my problems have started,> I initialy replicated all the IIS setting and NTFS permission from my> NT box on my 2003 box but so far have been unable to achive the same> result, it appaers that I can only grant anonymous write access if I ...
    (microsoft.public.inetserver.iis.ftp)
  • Re: GRANT VIEW SERVER STATE to Database Role
    ... Since it is a Server permission, it has to be a login. ... If you have several domain logins to which you want to grant this permission, but you would like to limit how many times you grant this on the server, do the following. ...
    (microsoft.public.sqlserver.programming)
  • RE: Distributed query
    ... If you would like to use Windows login without AD to access linked servers, ... First create a login on the remote server, double click the login, switch ... Microsoft Online Community Support ...
    (microsoft.public.sqlserver.security)
  • Re: Client cant see login box?
    ... I get a login challenge for that link ... To find the best Newsgroup for FrontPage support see: ... |>you're trying to do on,presuming a Windows server?). ... |>and yes, I cleaned out history, internet cache and temp internet files; ...
    (microsoft.public.frontpage.client)