SQL Server v14 - Login Failure

Question

We are using SQL server 2014, we update a password for a user login here

we click properties on a given login account and change the password here

we are certain we entering it correctly when we then try to login to the server using the login and password we get the following error

I don't know where else to look and what else to change -- when we do the same process on a different server it works without issue. But here its failing.

Where should we look and what else do we need to troubleshoot. Could it be we don't have access to certain databases, how do we fix this.

These are some of the errors coming through in the system logs

Message Login failed for user ‘abc’. Reason: Failed to open the explicitly specified database ‘AMS'. [CLIENT---]

Message Login failed for user 'abc’. Reason: Failed to open the database ‘Budget’ specified in the login properties. [CLIENT: ---]

Message Login failed for user ‘abc’. Reason: Password did not match that for the login provided. [CLIENT: ----]

Answer 1

You must set a default database where the user AND relative login can access.

Logins are objects with a password Users are objects, mapped to Logins, added to SQL Server entities.

• Under Server -> Security -> Logins
• Unser Database -> Security -> Users

Check also if the user is correctly mapped to the login.

To solve your problem you can set the master database as the default for your login.

Answer 2

You can find out more about the error in the SQLServer logs as described here https://learn.microsoft.com/en-us/sql/relational-databases/performance/view-the-sql-server-error-log-sql-server-management-studio?view=sql-server-ver16.

If you are looking for a quick fix to your problem, you can either:

• assign more server roles to your login
• or grant it an individual permission

use master;
grant CONNECT ANY DATABASE TO <login>;