Sql Server Login

  • Hi,

    All the logins (windows and sql logins)on the sql server work when I RDP onto the server but they do not work from the SSMS on my local machine. The only time these logins work from my local machine is when I make the login sysadmin.

    I have never seen something like this. Any ideas?

    Thanks.

  • please post the exact error you get.

    ---------------------------------------------------------------------

  • Login failed for user. error 18456 when the user is not sysadmin. If I grant the user sysadmin previleges it's working fine.

  • that error will come with a value for 'state', what is that equal to, and what message does SSMS give you?

    ---------------------------------------------------------------------

  • sql_novice_2007 (2/23/2014)


    Login failed for user. error 18456 when the user is not sysadmin. If I grant the user sysadmin previleges it's working fine.

    Have you checked the default_database settings for this login? Does this login has a corresponding user in its default_database (in case the default_database is a user database & not a system database) & it has appropriate permissions on this database?


    Sujeet Singh

  • Yes.I checked all of the above.I can see the logins work from rdp session,but not from the ssms from the local machine.

  • Look in the SQL Server error log, what is the exact message which gets recorded for the login failure?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Following is the error message is error log.

    Login failed for user 'UserTest'. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors.

    Thanks.

  • Local machine and server on the same domain?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Yes.

    I tried the following and it worked.

    GRANT CONNECT SQL TO [Testuser]

    GRANT CONNECT ON ENDPOINT::"TSQL Default TCP" TO [TestUser]

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply