SQL SERVER – Difference Between Login Vs User – Security Concepts

SQL SERVER – Difference Between Login Vs User – Security Concepts

In the last 13 years of blogging, I have been asked this question again and again and so many times that I think if you wake me up in the middle of the night I can easily reply about the difference between Login Vs User – Security Concepts.

Before I continue explaining the difference, I request you to read my earlier blog post on the same subject for additional clarity: SQL SERVER – Difference Between SQL Login and SQL User – SQL in Sixty Seconds #070.

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 is created at the SQL Server instance level and User is created at the SQL Server database level. We can have multiple users from a different database connected to a single login to a server.

Here is a simple image explaining the relationship of Login Vs User in SQL Server instances and database.

Here is the sample script to create a login and map a user to it.

1 2 3 4

USE master; CREATE LOGIN [MarkSmithL] WITH PASSWORD = 'password'; USE mydb; CREATE USER [MarkSmithU] FOR LOGIN [MarkSmithL];

Let me know if you have any further questions about Security Concepts. I will be happy to answer in the comment. Here are a few additional blog posts on the same subject:

SQL SERVER – Simple Script to Create a Login and User for a Specific Database with System Admin Rights A very popular question I often receive about SQL Server security is what is the difference between SQL Server Login and SQL Server User. I really love this question as I bet only 5% of SQL Server professionals I met know the answer to this question. In this blog post, we are going to see a Simple Script to Create a Login and User for a Specific Database with System Admin Rights

 

 

SQL SERVER – Difference Between SQL Login and SQL User – SQL in Sixty Seconds #070

“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.

https://www.youtube.com/watch?v=kLAgkMd8njE

 

Difference between a User and a Login in SQL Server

A "Login" grants the principal entry into the SERVER.

A "User" grants a login entry into a single DATABASE.

One "Login" can be associated with many users (one per database).

Each of the above objects can have permissions granted to it at its own level. See the following articles for an explanation of each

• Principals
• Database Users

 

 

作者:Chuck Lu    GitHub