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 video which describes the same with an easy example:

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

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.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.