Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


SQL Server Login versus SQL Server User
Published: Dec 03, 2020
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.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.