Whenever I present demo scripts, I often set up Users, mentioning “without Login”. This raised an intriguing question recently: What’s the distinction between Logins and Users in SQL Server? Answering this via text was challenging, but I managed a concise yet accurate response. So, I’ll break it down in a simplified manner here.

Essentially, a Login pertains to the SQL Server level, while a User is specific to the Database level.

An easy way to visualise this is: you “Login” to a Server and “Use” a Database. As such, a Login typically has roles and database permissions assigned, while a User is designated particular rights within a specific database.

To clarify, let’s consider a few examples:

When setting up a Login in SSMS, you’ll notice all references are server-centric, indicating that the Login provides overarching access to the SQL Server:

This allocation of rights is evident at the server stage:

Here’s where it gets interesting. In User Mapping, as the title implies, you’re linking the Login to individual DATABASE USERS. Consequently, when you assign a Login to a database, a corresponding User is generated within that database.

For instance, let’s say we make a new Login titled “myTestLogin”:

If this Login isn’t associated with any databases:

The result is a SQL Server Login that provides entry to the server but prohibits database access:

To rectify this, if we want both server and database access, we navigate to User Mapping and select a database. This action establishes a User in the chosen database, which associates with our Login:

This allows us to define the permissions for the User within that specific database:

Furthermore, we can grant access limitedly to certain entities, for instance:

grant select on sales.salesOrderHeader to myTestLogin

However, it’s essential to understand that it’s the User, not the Login, that’s granted database access.

Upon accessing the SQL Server using the Login, we can view the AdventureWorks database and recognise ourselves as a User within – which aligns with expectations:

I hope this clarifies the foundational differences between SQL Server Logins and Users.