SQL Server error 18456 — login failed for user

Any 18456. Always read the state first — it turns this from guesswork into one action.

1. Read the server's own error log, which is the only place the real state appears.

   Command PromptCopy

   EXEC xp_readerrorlog 0, 1, N'Login failed';

   The client is told State 1 on purpose, so an attacker cannot tell 'no such user' from 'wrong password'. The server log has no such restriction.

2. Confirm the server accepts SQL logins at all. Mode 1 is Windows-only, 2 is mixed.

   Command PromptCopy

   SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS WindowsAuthOnly;

3. If it is Windows-only and you need a SQL login, switch to mixed mode — this needs a service restart.

   Command PromptCopy

   EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2;

4. Check the login exists and is not disabled or locked out.

   Command PromptCopy

   SELECT name, is_disabled, LOGINPROPERTY(name,'IsLocked') AS IsLocked, LOGINPROPERTY(name,'IsExpired') AS IsExpired FROM sys.server_principals WHERE type IN ('S','U','G');

5. Enable and unlock as needed.

   Command PromptCopy

   ALTER LOGIN [appuser] ENABLE;ALTER LOGIN [appuser] WITH PASSWORD = 'NewStrongPassword' UNLOCK;

6. For state 11 or 12, the Windows account authenticated but has no SQL login — create one.

   Command PromptCopy

   CREATE LOGIN [EXAMPLE\AppService] FROM WINDOWS;

EXEC xp_readerrorlog 0, 1, N'Login succeeded';

State 38 or 40 — the login is fine but the database it wants is not.

1. Find which database is being asked for.

   Command PromptCopy

   EXEC xp_readerrorlog 0, 1, N'Login failed', N'38';

2. Check the database exists and is online.

   Command PromptCopy

   SELECT name, state_desc, user_access_desc, is_read_only FROM sys.databases ORDER BY name;

3. Bring it online if it is not.

   Command PromptCopy

   ALTER DATABASE [AppDb] SET ONLINE;ALTER DATABASE [AppDb] SET MULTI_USER;

4. Check the login's default database — a login pointing at a database that has been dropped fails before it can connect anywhere.

   Command PromptCopy

   SELECT name, default_database_name FROM sys.server_principals WHERE type IN ('S','U');

5. Point it somewhere that exists.

   Command PromptCopy

   ALTER LOGIN [appuser] WITH DEFAULT_DATABASE = [master];

6. Map the login to a user in the database if it is not already.

   Command PromptCopy

   USE [AppDb];CREATE USER [appuser] FOR LOGIN [appuser];ALTER ROLE db_datareader ADD MEMBER [appuser];

SELECT DB_NAME() AS CurrentDb, SUSER_NAME() AS LoginName;