Login Failed or Permission Denied in SQL Server

Step 1: Is This a Connection Failure or an Authentication Failure?

The message already tells you, and it is the only thing on this page that costs nothing to check. If it says the network path was not found, that the target machine actively refused the connection, or that it could not locate the server or instance specified, then nothing on the far side ever answered. No credentials were sent, no login was looked up and no permission was evaluated. That is a service, port, firewall or name problem, and Cannot Connect to SQL Server is the page that owns it, start to finish.

The tell is whether the message names a principal. Login failed for user 'DOMAIN\svc_app' means SQL Server received the connection, read the credentials and decided against them. From that point the network is proven working and every remaining check is a security check. Everything below assumes you are on this side of that line.

Step 2: For 18456, Read the State From the ERRORLOG

18456 is one error number covering roughly a dozen unrelated causes, and the client is deliberately told none of them. It almost always receives State 1, because explaining the failure precisely would hand an attacker a checklist. The real state goes to the server error log, and until you have read it you are guessing.

Run this on the instance. It pulls the current error log into a table variable so one pass returns both halves of each failure, the numbered Error: line and the human Reason: line, which are written as separate entries and are close to useless apart.

SET NOCOUNT ON;
DECLARE @log TABLE (LogDate datetime2(0), ProcessInfo nvarchar(64), Text nvarchar(max));
INSERT INTO @log EXEC sys.xp_readerrorlog 0, 1;

SELECT LogDate, Text
FROM   @log
WHERE  Text LIKE 'Login failed%'
    OR Text LIKE 'Error: 18456%'
ORDER BY LogDate DESC, Text;

The two lines pair up by timestamp. Read the state off the Error: line and the cause off the Reason: line beside it, and note the client address, which is usually the fastest way to find out what is still knocking.

Now take the state to the right place. The short version is below, and Troubleshoot Login Failed for User (Error 18456) decodes every state including the Azure and contained-database ones, which is where to go when yours is not in this table.

If what you are looking at is a pattern rather than one failure, a burst of states 8 and 5 from a single address at three in the morning, treat it as a security event and not a configuration one. DBA Scripts: Get Login Security Audit summarises failed logins out of the error log by login and client so the shape is obvious, and flags weak login settings while it is in there. Microsoft’s own list of states is in MS Docs for MSSQLSERVER_18456.

Step 3: The Login Exists and the Database Refuses It

This is the family that gets misdiagnosed most often, because several of these errors still print the words “login failed” even though authentication already succeeded. Nothing here is fixed by a password.

Cannot open database requested by the login (4060 and 4064). The connection named a database, the engine authenticated the login, and the database then turned it away: no user mapped to that login, or the database is offline, in single user mode, or spelled wrong in the connection string. The ERRORLOG records it as 18456 state 38 with the database named. Cannot Open Database Requested by the Login (Errors 4060 and 4064) covers both, and 4064 is the version where the login named no database at all and its default one is unusable, which is one ALTER LOGIN to fix.

The server principal is not able to access the database (916). Same root cause, different surface. The login is connected to the instance and something asked about a database it has no user in, which is why Object Explorer triggers it for databases you were not even looking at. Error 916 has the full picture and the fix, which is a user mapping and never a password reset.

The account is disabled (18470). The friendliest member of the family, because it says exactly what is wrong. Enabling it is one statement; the question worth asking is why it was switched off, and modify_date in sys.server_principals dates the change. Login Failed: The Account Is Disabled (Error 18470) covers both halves. If the password is also wrong you get 18456 state 7 instead, and you have two faults to fix, not one.

Orphaned users after a restore (15023, and 18456 state 62 on contained databases). A database restored from another server brings its users with it, and their SIDs no longer match any login here. The application cannot log in, somebody tries to create the user, and the engine says the user already exists. Do not drop it. User, Group or Role Already Exists (Error 15023) explains why ALTER USER ... WITH LOGIN is the right statement, and DBA Scripts: Get Orphaned Users finds every one of them across the instance before the users do. On an Availability Group the same problem arrives by a different road, because the databases fail over and the logins do not: Creating SQL Logins on an AG Environment is the way to create them so the SIDs match on every replica.

Step 4: The Login Works and One Statement Is Denied

By now authentication has succeeded and the database has let the principal in. What is left is authorisation on a single action, and these messages are the most precise on the whole page: they name the permission, the object, the schema and the database. Take them literally.

Before changing any grant, find out what the principal can actually do. Run this from your own session and impersonate theirs, which is far quicker than borrowing their credentials and tells you the same thing.

USE YourDatabase;
EXECUTE AS USER = 'TheUser';

SELECT USER_NAME()                                        AS RunningAs,
       HAS_PERMS_BY_NAME('dbo.YourTable','OBJECT','SELECT') AS CanSelect,
       HAS_PERMS_BY_NAME(DB_NAME(),'DATABASE','CONNECT')    AS CanConnect,
       IS_ROLEMEMBER('db_datareader')                       AS InDbDatareader;

SELECT DISTINCT permission_name
FROM   sys.fn_my_permissions('dbo.YourTable','OBJECT')
WHERE  subentity_name = '';

REVERT;

Connect is granted, select is not, and fn_my_permissions returns nothing at all for the object. That is the whole diagnosis. Trust the second query when the two disagree: it lists what is effective after role membership, inheritance and denies have all been resolved, which is the arithmetic people get wrong in their heads.

When a grant appears to do nothing, look for a DENY, because a DENY beats every GRANT no matter where the GRANT came from. This finds them.

SELECT dp.state_desc, dp.permission_name,
       OBJECT_SCHEMA_NAME(dp.major_id) + '.' + OBJECT_NAME(dp.major_id) AS ObjectName,
       pr.name AS GrantedTo
FROM   sys.database_permissions AS dp
JOIN   sys.database_principals  AS pr ON pr.principal_id = dp.grantee_principal_id
WHERE  pr.name = 'TheUser'
ORDER BY dp.state_desc DESC;

A principal in that state comes back with two rows: a GRANT of CONNECT on the database, and a DENY of SELECT on the object. The DENY is the one winning, and adding another GRANT will not change that.

Then go to the page for the message in front of you. The SELECT Permission Was Denied on the Object (Error 229) is the object-level case and the one to read on fixing at the right level, role membership ahead of schema grants ahead of object grants. CREATE DATABASE Permission Denied (Error 262) is the statement-level version, and the same message shape covers every other CREATE verb. Grant VIEW SERVER STATE is the one that stops a DBA reading the DMVs on a server they can otherwise connect to perfectly well.

Two more sit in this step because they look like permission problems and are really ownership problems. Cannot Execute As the Database Principal (Error 15517) is almost always a restored database whose owner SID no longer resolves, and it breaks jobs and EXECUTE AS rather than connections. The Database Principal Owns a Schema (Error 15138) is the one that blocks cleanup, where a user cannot be dropped because dropping it would leave a schema unowned.

For what a single login reaches across the whole instance, DBA Scripts: Get User Permissions Audit answers it one login at a time. The two functions used above are HAS_PERMS_BY_NAME and sys.fn_my_permissions in MS Docs.

Step 5: Windows Authentication Has Its Own Failures

If the login is a domain account, three errors belong to the authentication layer rather than to SQL Server, and no amount of work inside the instance will fix them.

The login is from an untrusted domain (18452). The word domain sends everybody to the domain admins, and most of the time the domain is fine. Start at the client instead: whoami says whether the session is even running as a domain account, and an IP address in the connection string forces the NTLM path and can produce this on its own. One failing machine is a client problem. Many at once earns the call. Login Failed: The Login Is From an Untrusted Domain (Error 18452) has the sequence.

Cannot generate SSPI context, SSPI handshake failed (17806). Kerberos could not issue a ticket. Check the clock first, because five minutes of skew breaks Kerberos outright and costs nothing to rule out, then look for duplicate or missing SPNs. SSPI Handshake Failed in SQL Server (Error 17806) is the diagnosis in order.

It works when I run it locally and fails from the application server. That is usually the double hop: the client authenticates to one server, that server tries to pass the identity on to SQL Server, and NTLM cannot delegate it. The shape is distinctive, an account that works interactively and fails from a linked server or a middle tier. Kerberos vs NTLM in SQL Server has the one query that tells you which scheme your connections are really using, which is worth running before an incident rather than during one.

State 58 belongs here too. A SQL login against an instance set to Windows authentication only fails with 18456 state 58, and the fix is a decision about the server’s authentication mode, not about the login.

Step 6: Nobody Can Get In At All

When every login is failing, including yours, the instance is usually not refusing people one at a time. It is refusing everybody for one reason.

Server is in script upgrade mode (18401). Right after a cumulative update or a service pack, SQL Server runs its upgrade scripts against the system databases before it will accept normal connections, and every login gets 18401 until it finishes. The instance is working. It is not finished starting, so watch the error log rather than the clients, and give it the time the scripts need. Once they finish, Login Failed: Server Is in Script Upgrade Mode (Error 18401), and How Long to Wait Before You Worry shows how to watch the progress in the error log and what to do when it stops moving.

Locked out with no sysadmin left. It happens: the only sysadmin account was a person who left, or a hardening change removed the local Administrators group. The honest answer is that there is a supported way back in and it needs downtime. Start the instance in single user mode with the -m startup parameter, connect from the server itself as a member of the local Administrators group, which SQL Server admits as sysadmin in that mode, create or fix a login, then restart normally. It is a short, well documented procedure, covered in MS Docs on starting SQL Server in single user mode, and the only connection allowed while it runs is yours, so plan the outage rather than improvising it.

The cheaper version of that story is knowing who holds sysadmin before anybody leaves. DBA Scripts: Get Sysadmin Members lists every member of the role including the ones that arrive through a Windows group, which is where the surprises usually are.