The Two-Layer Model Explained
“We added them to the database, but they still can’t connect.”
Sound familiar? That’s the kind of confusion SQL Server’s two-layer security model creates when people don’t understand the difference between a login and a user.
Let’s clear that up, because getting it wrong causes broken access, orphaned users, and frustrated help desk calls.
Two Layers: One to Get In, One to Do Anything
SQL Server controls access at two separate levels:
- Login (Server-Level)
- Exists at the SQL Server instance level
- Allows someone or something to connect to the server
- Can be:
- SQL login (username + password inside SQL)
- Windows login (domain account)
- Windows group (preferred for manageability)
But a login alone doesn’t grant access to databases.
- User (Database-Level)
- Exists inside a specific database
- Grants access to that database and defines roles/permissions
- Must be mapped to a login, unless you’re using a contained database
So:
Login = authentication (Can you connect?)
User = authorization (What can you do inside the database?)
What Happens When They Don’t Match?
Here’s a common scenario:
You restore a database from Prod to Test. A user exists in the database, but the matching login doesn’t exist on the new server.
Result? You’ve got an orphaned user.
SQL Server sees the user in the database, but there’s no login at the instance level with the same SID. The connection fails—even though everything “looks right.”
How to Fix Orphaned Users
Run this at the database level to find all orphaned users:
EXEC sp_change_users_login 'Report';
(Deprecated, but still handy in older versions.)
For newer systems, use:
Use MyDatabase;
GO
SELECT dp.name AS UserName
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S', 'U') AND sp.sid IS NULL;
Use this command to remap a user to an existing login:
ALTER USER [username] WITH LOGIN = [loginname];
Or…this is a process I’ve been using for 20 years:
Transfer logins and passwords between instances – SQL Server | Microsoft Learn
Why You Should Care
- Granting a login access to the server doesn’t give them database access.
- Creating a user in a database without a login won’t allow connections.
- Orphaned users silently break apps after restores or migrations.
- Unused users or logins clutter security and increase audit risk.
Best Practices
- Use AD groups as logins whenever possible. Much easier to manage access for teams.
- Don’t grant permissions to logins directly. Always go through database users and roles.
- When dropping users or logins, clean up both sides: DROP USER and DROP LOGIN.
- Document mappings so you know which logins tie to which databases.
Database Emergencies Cost Thousands Per Minute
When SQL Server fails, you need expert help fast.
For a limited time: First month of Pocket DBA® FREE with a 6-month plan.
• Senior DBAs available 24/7 • 1-hour SLA • Proactive problem prevention
Don’t wait for a disaster. Be prepared.
CLAIM FREE MONTH
Limited availability.
Thanks for reading!
–Kevin
