How To Fix Login Failed for User Microsoft SQL Server Error 18456 Step-By-Step

Play How To Fix Login Failed for User Microsoft SQL Server Error 18456 Step-By-Step
Sign in to queue

Description

In this post, I will teach you how to determine what user has administrative rights on the server as well as walk you Step-By-Step how to configure SQL Server to allow other users to login as Administrators to SQL Server.  The generic message "Login Failed for User (Microsoft SQL Server, Error: 18456)" means you entered invalid credentials when logging into SQL Server.  In the below screen shot, I am logging into Microsoft SQL Server Management Studio with a user that does not have administrative permissions to connect to the server. Stick around after you fix this problem to learn how you can avoid many potential problems in the near future by learning a few fundamentals about dealing with SQL Server and inevitable SQL server issues.

SNAGHTML1d3c8af8

You need to figure out what user does have rights to SQL server.  Usually this is the user that you logged in with when you installed SQL Server or the user that is the default administrator on the computer.  To get a list of users on the machine... For full blog article navigate to: How to Fix Login Failed for User (Microsoft SQL Server, Error: 18456) Step-By-Step – Add SQL Administrator to SQL Management Studio

Web Analytics Clicky

Embed

Download

Download this episode

The Discussion

  • User profile image
    ahmed

    from login - properties - securable --> mark the permission for ( control server )
    this solve the error ISA

  • User profile image
    matay

    Right click server name from the SSMS and go to server properties. Click security and enable Wndows and SQL Server Authentication mode. This solution is to the point and will fix the problem.

  • User profile image
    jun

    Thank you matay ! I did that before on my local PC but I forgot completely.

  • User profile image
    raps

    Thanks matay. Been a awhile since I set up a new db server.

  • User profile image
    danstolts

    If this helped, Please rate using stars above.

    Thanks. 

  • User profile image
    Val

    Nah, mate its a no go with this method.

  • User profile image
    Val

    Just an update, here is the solution if anyone else has there error with a properly configured login:

    If you're trying to connect using "SQL Server Authentication" then you may want to modify your server authentication:

    Within the Microsoft SQL Server Management Studio in the object explorer:

    Right click on the server and click Properties

    Go to the Security page

    Under Server authentication choose the SQL Server and Windows Authentication mode radio button

    Click OK

    Restart SQL Services

  • User profile image
    DRepain

    Thanks This was very helpful

  • User profile image
    Yavuz

    Matay! thank you, it worked for me too :)

  • User profile image
    sandipapatel
    Just enable dual authentication mode and restart the service - it works like charm!
    Thanks.
  • User profile image
    Freddy

    Hey everyone! I am receiving the error code 18456 using local administrator & domain administrator so i cannot even login into SSMS. Any ideas? Thanks

  • User profile image
    Nathan

    Freddy - I was running into this issue too. Have you tried launching SSMS using "Run as Administrator" then signing in?

  • User profile image
    Ryan

    Nathan - Launching SSMS using "Run as Administrator" resolved the issue for me! I'm not certain as to why that fixes it but it works!

  • User profile image
    Todd McDaniel SQL DBA

    An application should NEVER log into a database as administrator and application users should never have administrator or even DB_OWNER level permissions to a database. This is very bad coding practice to do this and a big security risk.

    Application users should have Read or Read/Write/Execute permissions to the specific tables and procedures necessary, preferably controlled by user groups.

  • User profile image
    obeid

    when i finsh install SQL 2014 then i want run it i get this error i can't login how i can' do all solv taking about chang permmition but i cant connect with server any idea

Add Your 2 Cents