“What is the difference between SQL Login and SQL 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 best 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.
Basic Explanation SQL Login and SQL User:
SQL 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.
Action Item
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 (https://blog.sqlauthority.com)
11 Comments. Leave new
Quick and easy to understand your explanation of an article …Thanks @Pinal
Eng Pinal I’m try this tutorial in sql server 2012 in new user properties –> Owned Schema. Can’t find Human resource.Can you help me please?
This is useful and simple, Pinal. I didn’t realize that I didn’t know this. I’ve always just used a script to create both a login and a user for what I needed and never really thought about the distinction. Your simple description of authentication and authorization is valuable.
I have DB1.tableA , & StoreprocA( this one contains update DB1.tableA & update DB2.tableB,update DB2.tableC)
have userA, UserB – with db_owner for DB1 & DB2
my requirment is: userA only update the tableA not userB ( through SQL statement and Storeprocedure)
I pass below command
(deny update on DB1.dbo.tableA to UserB)
(deny execute on object::DB1.dbo.StoreprocA)
output: can’t update through SQL statement but storeprocedure still updates.
“We can have multiple user from different database connected to a single login to a server.”
I think multipe logins to the server can have one user to the database.
Example : login 1 / login 2 / login 3 associated to a user (DBA) having such role and permissions on such or such DB.
Correct me if I am wrong
said – I am not clear with your question. Can you please rephrase?
thanks.. nice explanation …
Rikkee – thanks!
Daaaaaaaatttt issssss verry good videoooo
Thankssssss