SQL Server Login versus SQL Server User
Published: Nov 28, 2021
In a good many of my demo scripts I create Users with the caveat “without Login” which led to someone asking me the other day what the difference is in SQL Server between Logins and Users. This was actually asked via text which resulted in a somewhat stunted answer but, in essence, an accurate one and therefore I’m going to keep this relatively short as well…

In a nutshell, a Login is SQL Server level and a User is Database level.

Think of it this way… you Login to a Server, but you Use a Database. Therefore a Login will generally be allocated roles and database access, and a User will be given specific access within the database.

To save me phrasing in numerous ways I’ll just do a couple of examples:

Creating a Login in SSMS you can see that all references are to the server level and imply logging into the SQL Server itself to gain a high level access:

This is also reflected in the allocation of access at the server level:

Now… here’s the fun part… when you go to User Mapping, as the name suggests, we’re mapping the Login to USERS in each database. Therefore as we click on each database we wish to allocate to the Login, a connected User is created in the database.

Example, if we create a new Login called myTestLogin:

But we don’t allocate it to any databases:

We now have a Login in SQL Server which lets us access the server, but we cannot access any databases:

So… we now want to be able to access the server and be able to access a database. So, if we go to User Mapping and then check a database this will create us a User in that database which is linked to our Login:

Now we can access rights for that User within the database:

We can also grant specific access only to specific objects if we wish:

grant select on sales.salesOrderHeader to myTestLogin

But the key part is that database access is granted to a User and not a Login.

Accessing the SQL Server via the Login we can now see the AdventureWorks database and see ourselves as a User within it, which is exactly as we would expect:

That was a little waffly but hopefully that explains the fundamental difference between a Login and a User.

NB: Comments will only appear once they have been moderated.