locked
Login Issue with SQL Server 2008 RRS feed

  • Question

  • Hi,

     

    I have a Test Server running on Windows Server 2008 R2 with SQL Server 2008 Enterprise Edition 64 bit with SP1. Normally I use a Domain Account for testing which has sysadmin access on the server.

     

    But I have a scenario where I need to test some feature by granting the login with the least privileges. So I mapped the new login to Public on the Server Roles page while creating the new login(Windows Login) along with appropriate permission on the database and required objects.

     

    If I connect locally from the server it works. 

     

    When I tried to connect to the Server from my client with the new login, I am getting the error Login failed for user 'Redmond\xyz'.

     

    In the event viewer I am getting this message Login failed for user 'Redmond\xyz'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors

     

    Adding the login to sysadmin server role or granting the login with CONTROL SERVER Permission solves this issue but this will not be a recommended practice.

     

    Is there any workaround for this?

    Tuesday, June 15, 2010 12:23 PM

Answers

  • What if you run

    GRANT CONTROL SERVER TO 'Redmond\xyz'

     

    • Proposed as answer by Tom Li - MSFT Thursday, June 17, 2010 7:30 AM
    • Marked as answer by Tom Li - MSFT Monday, June 21, 2010 2:12 AM
    Wednesday, June 16, 2010 7:27 AM
  • Go to the login property / securables page of Redmond\xyz and see if the endpoint TSQL Default TCP and TSQL Namedpipes being denied CONNECT permissions explicitly.


    Thanks, Leks
    • Proposed as answer by Tom Li - MSFT Thursday, June 17, 2010 7:30 AM
    • Marked as answer by Tom Li - MSFT Monday, June 21, 2010 2:12 AM
    Wednesday, June 16, 2010 10:16 PM

All replies

  • What if you run

    GRANT CONTROL SERVER TO 'Redmond\xyz'

     

    • Proposed as answer by Tom Li - MSFT Thursday, June 17, 2010 7:30 AM
    • Marked as answer by Tom Li - MSFT Monday, June 21, 2010 2:12 AM
    Wednesday, June 16, 2010 7:27 AM
  • Go to the login property / securables page of Redmond\xyz and see if the endpoint TSQL Default TCP and TSQL Namedpipes being denied CONNECT permissions explicitly.


    Thanks, Leks
    • Proposed as answer by Tom Li - MSFT Thursday, June 17, 2010 7:30 AM
    • Marked as answer by Tom Li - MSFT Monday, June 21, 2010 2:12 AM
    Wednesday, June 16, 2010 10:16 PM