SQL Server Login Failed for User: Fix in Seconds

The Most Common Cause: The Login Is Disabled or Expired

I've seen this hundreds of times. You're trying to connect to SQL Server Management Studio (SSMS) or an app, and you get the dreaded 18456 error. The user is fine, you think. But nine times out of ten, the SQL Server login is simply disabled or the password has expired. SQL Server doesn't give you a friendly warning—it just says 'Login failed'. Infuriating, I know.

Here's the fix. Open SSMS with a sysadmin account (like sa or a Windows admin). Right-click the server in Object Explorer, go to Security > Logins. Find the user that's failing. Right-click, choose Properties. Under Status, make sure 'Login' is set to Enabled. Also check 'Password' — if 'Enforce password expiration' is checked and the password has aged out, uncheck it for testing, or reset the password.

Once enabled, try connecting again. I've fixed this exact scenario for a client's finance app in under 30 seconds. It's almost always the first thing to check.

Second Most Common Cause: Orphaned User in the Database

This one tripped me up the first time too. The login exists at the server level, but the database user it's supposed to map to is orphaned. That means the SID (security identifier) stored in the database doesn't match the SID of the server login. SQL Server then says 'nope' — and you get error 18456.

You'll know this is the problem if the user can connect to the server but not to a specific database. Or if you see the user listed in SSMS under the database with a red X. The fix? Use the sp_change_users_login stored procedure or the newer ALTER USER command.

Run this in the context of the database where the user is failing:

USE YourDatabaseName;
GO
ALTER USER YourUserName WITH LOGIN = YourLoginName;
GO

This remaps the database user to the server login. If you're unsure of the login name, query sys.database_principals and sys.sql_logins to compare SIDs. I prefer the ALTER USER approach because it's clearer, but the old sp_change_users_login still works:

EXEC sp_change_users_login 'Auto_Fix', 'YourUserName';

I've used this on SQL Server 2016, 2019, and 2022. It works across all versions. After remapping, the user can access the database immediately.

Third Most Common Cause: Wrong Authentication Mode

Another sneaky one. SQL Server can run in two authentication modes: Windows Authentication only, or Mixed Mode (Windows + SQL Server Authentication). If your app or user is trying to use SQL Server authentication (username and password) but the server is set to Windows-only, you'll get error 18456. The server never even checks the password—it just rejects the login type.

To check, right-click the server in SSMS, choose Properties, then go to Security. Under 'Server authentication', you need 'SQL Server and Windows Authentication mode'. If it's set to 'Windows Authentication mode' only, that's your problem.

Change it, then restart the SQL Server service. You can do this from SSMS or via the SQL Server Configuration Manager. Restarting is mandatory—the change doesn't take effect until the service restarts. I've seen people forget this step and waste hours troubleshooting.

One quick note: if you're connecting from a remote machine, also verify that SQL Server has TCP/IP enabled in SQL Server Configuration Manager. That's a separate issue, but it can mask as error 18456 when the client can't even reach the server.

Quick-Reference Summary Table

Error 18456 is frustrating, but it's almost always one of these three things. Start with the login status, then check for orphaned users, then verify the auth mode. You'll be back up in minutes.