SQL Server Logins vs Users: What Is the Difference?

SQL Server security becomes much easier to understand once you separate two questions: Who can connect to the SQL Server instance? And what can that identity do inside a particular database? Logins and users answer those questions at different scopes. For a focused explanation, see DBA responsibilities.

A login exists at the SQL Server level

A login is a server-level security principal used to authenticate or represent an identity at the SQL Server instance. Depending on the environment, that can be a Windows identity, a SQL-authenticated login, or another supported identity model.

A login can exist even if it has no access to a particular user database.

A database user exists inside a database

A database user is a database-level principal. It represents an identity within that specific database and is the principal to which database roles and permissions can be assigned.

One login may map to users in several databases. The permissions can be different in each database.

A practical example

Imagine a login named AppReporting. At the server level, SQL Server knows that the login exists. In the Sales database, AppReporting may map to a database user that belongs to a read-only role. In another database, there may be no user at all, so the login cannot automatically read that database.

CREATE LOGIN [AppReporting]
WITH PASSWORD = 'Use-a-secure-secret-here';

USE [Sales];
CREATE USER [AppReporting]
FOR LOGIN [AppReporting];

Then grant only what is needed

Instead of making the application account a database owner, assign a role or explicit permission that matches the job. For reporting, that might be a carefully designed read-only role. For an application, permissions may be limited to selected schemas, tables or stored procedures.

What is a SID and why does it matter?

SQL Server internally uses security identifiers to associate server logins and database users. When a database is restored or moved to another instance, the database user can arrive with its original SID while the destination server has no matching login, or has a login with a different SID. Explore DBA roadmap for more detail.

That situation creates what is commonly called an orphaned user.

Why orphaned users happen

A database backup contains database users, but server-level logins live outside the user database. If you move the database without also recreating the relevant logins correctly, the database user may no longer map to the intended server principal. The companion article on backup and recovery develops this point.

How to inspect mappings

SELECT dp.name AS DatabaseUser,
       dp.type_desc,
       dp.sid
FROM sys.database_principals AS dp
WHERE dp.type IN ('S','U','G')
ORDER BY dp.name;

At server level, sys.server_principals exposes server principals. Comparing the expected identity and SID helps diagnose mapping problems.

Roles are not the same thing as users

A role is a container for permissions. Instead of granting the same permissions individually to ten users, you can grant them to a database role and add the appropriate users to that role. This makes security easier to review and maintain.

GRANT, DENY and REVOKE

GRANT gives a permission. DENY explicitly prevents it and can override permissions inherited through many paths. REVOKE removes a previously granted or denied permission so normal permission evaluation can continue. These operations should be used deliberately because complex permission combinations become difficult to reason about.

Common security mistakes

• Adding application accounts to sysadmin because troubleshooting is faster.
• Using db_owner for normal application access.
• Creating SQL logins with weak or shared passwords.
• Migrating databases but forgetting the associated server logins.
• Assuming a login automatically has access to every database.
• Granting permissions directly to many individuals when a role would be easier to manage.
• Failing to test access while impersonating or connecting as the actual account.

Contained users

SQL Server also supports contained database users in certain scenarios, which reduce dependency on a traditional server login for authentication to that database. They are useful in some architectures, but they do not remove the need to understand principal scope and permission design.

A good troubleshooting sequence

1. Can the identity authenticate to the SQL Server instance?
2. Does a corresponding database user exist?
3. Does the mapping point to the expected identity?
4. Which roles does the user belong to?
5. Which explicit GRANT or DENY permissions apply?
6. Is ownership or schema context affecting the result?
7. Can the problem be reproduced using the actual identity?

The principle to remember

Related training

The SQL Server DBA course includes authentication, logins, users, roles, least privilege, orphaned-user scenarios and practical permission testing.

View the SQL Server DBA course

Need help choosing the right course?

If you are not sure where to begin, contact Goalbi Academy and tell us your current experience level and what you want to learn.

Contact Goalbi Academy

References

Create a Login - SQL Server

Database-Level Roles - SQL Server

Permissions (Database Engine) - SQL Server