Difference Between a User and a Login in SQL Server

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

Difference between login and user in sql server

Logins are a server wide (instance level) objects. 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.

Users are a database objects, correctly referred to as 'database principals' (see sys.database_principals). They are the recipients of database permissions, like create table or select.

Ordinarily a login is mapped 1-to-1 to a user in each database, via a matching SID, but there are some exception, like all members of the sysadmin fixed server role are always mapped to dbo.

Users without login are a specific construct for Service Broker remote identities (see Remote Service Bindings) and for code signing. You should never have to create one in any other context, and if you do, you're likely doing it wrong. Users without login are never meant to be impersonated.

SQL Server - Server Login vs. Database Login

A Server Login is for authentication. A Database User is for authorization.

In the very simplest terms, a server login allows you to connect to the SQL Server database server. It's an outer shell for authenticating credentials.

The database user, on the other hand, does not have proper credentials of its own and relies on the server login for authentication. The database user's is used directly for authorization, allowing rights to be granted to database objects such as procs, tables, views.

Difference between SQL Server Login Account & Database Users?

I think this is a very useful question. Just to add my two cents: from msdn:

A login is a security principal, or an entity that can be authenticated by a secure system. Users need a login to connect to SQL Server. You can create a login based on a Windows principal (such as a domain user or a Windows domain group) or you can create a login that is not based on a Windows principal (such as an SQL Server login).
Note Note

To use SQL Server Authentication, the Database Engine must use mixed mode authentication. For more information, see Choose an Authentication Mode.

As a security principal, permissions can be granted to logins. The scope of a login is the whole Database Engine. To connect to a specific database on the instance of SQL Server, a login must be mapped to a database user. Permissions inside the database are granted and denied to the database user, not the login. Permissions that have the scope of the whole instance of SQL Server (for example, the CREATE ENDPOINT permission) can be granted to a login.

http://msdn.microsoft.com/en-us/library/aa337562.aspx#Background

SQLServer difference between new login and new database user

A login is a login account for the entire SQL Server instance - an instance can contain numerous databases.

A user is defined at the database level, and is associated with a login to provide interactive access (privileges providing)

Logins are a server side (instance level) objects. 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.

Users are a database objects, correctly referred to as 'database principals' (see sys.database_principals). They are the recipients of database permissions, like create table or select.

Ordinarily a login is mapped 1-to-1 to a user in each database, via a matching SID, but there are some exception, like all members of the sysadmin fixed server role are always mapped to dbo.

Users without login are a specific construct for Service Broker remote identities (see Remote Service Bindings) and for code signing. You should never have to create one in any other context, and if you do, you're likely doing it wrong. Users without login are never meant to be impersonated.

---

Related Topics