The Server Principal Is Not Able to Access the Database (Error 916)

The login worked. That is the part that throws people. Authentication is already done by the time 916 fires, so resetting the password or checking the login’s status gets you nowhere. What failed is the next step: the server principal tried to enter a specific database, and that database has no user for it to become.

Why This Happens

A login and a database user are two different principals joined by a SID. Error 916 means the join failed for this database: either there is no user mapped to the login in it, the mapped user has had CONNECT denied or revoked, or the user was dropped while the login lived on.

The special case that generates most of the search traffic is SSMS Object Explorer. When you expand the Databases node, SSMS quietly queries every database for the detail columns it shows, size and collation among them. If your login lacks access to even one database on the instance, that background query fails and SSMS raises 916 at you, about a database you never clicked.

Reproduce It in One Batch

This is the exact test I ran to capture the message above, and it doubles as a diagnostic for any login you are troubleshooting:

EXECUTE AS LOGIN = 'ErrDemo916';
SELECT TOP 1 order_id FROM DemoDatabase.dbo.RlsOrders;
REVERT;

If the login has no user in the database, that SELECT throws 916 every time. EXECUTE AS lets you test as the affected principal without knowing their password.

Find the Gap

Check whether the login has a user in the database, and what that user can do:

USE DemoDatabase;
SELECT dp.name        AS database_user,
       dp.type_desc,
       sp.name        AS mapped_login,
       dp.authentication_type_desc
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON sp.sid = dp.sid
WHERE dp.type IN ('S', 'U', 'G')
ORDER BY dp.name;

No row with your login’s name in mapped_login means no mapping. A row whose database_user exists but whose CONNECT was revoked shows up in sys.database_permissions with state_desc = 'DENY' or simply no GRANT row for CONNECT.

The Fix

For the missing-user case, create the mapping and give it what it needs:

USE DemoDatabase;
CREATE USER ErrDemo916 FOR LOGIN ErrDemo916;
ALTER ROLE db_datareader ADD MEMBER ErrDemo916;

For a denied CONNECT:

GRANT CONNECT TO ErrDemo916;

For the Object Explorer variant, you have two honest options: grant the login access to the database SSMS is choking on, or stop SSMS asking the question. In Object Explorer Details (F7), right-click the column headers and remove Collation and the size columns, and the background query that triggers 916 stops running. Granting access is the fix; trimming the columns is the workaround when access is genuinely not supposed to exist.

When It Is Not This

If the database is OFFLINE, RESTORING or in SINGLE_USER you get different errors (926, 927, 924), not 916. And if the login itself failed you are in 18456 territory before any of this starts. 916 specifically means: server let you in, database did not.

Common Questions

Related Scripts

• Login Failed (18456), the server-door error this gets confused with
• Cannot Open Database (4060), the connection-string sibling
• Orphaned Users (15023), how mappings break after a restore
• Get User Permissions Audit, one login’s real access in a single script