RSS Feed

Difference between Database Logins & Users

Below you can find the difference between database users & logins in SQL Server 2008:

LoginUsers
Databases are owned by loginsAll objects in a database are owned by a user
A "Login" grants the principal entry into the SERVER. One "Login" can be associated with many users (one per database).A "User" grants a login entry into a single DATABASE.
Their correct name is 'server principals' (see sys.server_principals) Server wide privileges are granted to logins, like create database or view server state permissions. Their correct name is 'server principals' (see sys.server_principals) Server wide privileges are granted to logins, like create database or view server state permissions.
--An example of creating a Login in SQL Server 2008

Create login My_Login with Password = 'Pass123456@'
--An example of creating User for a particular Login in SQL Server 2008

use Northwind

CREATE USER My_User FOR LOGIN My_Login


An important point to understand:
One "Login" can be associated with many users (one per database).

Why do we have both login and a user?

One reason to have both is so that authentication can be done by the database server, but authorization can be scoped to the database. That way, if you move your database to another server, you can always remap the user-login relationship on the database server, but your database doesn't have to change.

Have a look at below links to learn more on database users & logins:

1. MSDN
2. SQL Server Central
3. What is the difference between dbo owner and the user that owns the database?
4. SQL Server Security Basics Logins vs Users
5. Difference between a user and a login in sql server
6. Difference between Login and user in sql server