“What is the difference between Login and User in SQL Server?”
This is a very common question I often receive. Yesterday when I was browsing Facebook, I once again noticed this question once again asked in SQLBangalore group. My very good friends – Vinod Kumar and Balmukund Lakhani had already answered the question there. However, I every time, I read this question, I realize that not everyone knows the basics of these two concepts. If I have to explain the difference between them, it may take a long time, but I will try to explain it at a very basic level.
SQL Server Login is for Authentication and SQL Server User is for Authorization. Authentication can decide if we have permissions to access the server or not and Authorization decides what are different operations we can do in a database. Login are created at the SQL Server instance level and User is created at SQL Server database level. We can have multiple user from different database connected to a single login to a server.
I hope this is clear enough. If not, I strongly suggest you watch following quick video where I explain this concept in extremely simple words.
Here are the blog posts I have previously written on the subject of security. You can read it over here:
- SQL SERVER – Importance of User Without Login
- SQL SERVER – Disable Guest Account – Serious Security Issue
- SQL SERVER – Say No to DB Data Roles – SQL Security – Notes from the Field #022
- SQL SERVER – Introduction to SQL Server Security – A Primer
You can subscribe to my YouTube Channel for frequent updates.
Reference: Pinal Dave (http://blog.sqlauthority.com)