Difference between a User and a Login in SQL Server

Question

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

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.

I think there is a really good MSDN blog post about this topic by Laurentiu Cristofor:

The first important thing that needs to be understood about SQL Server security is that there are two security realms involved - the server and the database. The server realm encompasses multiple database realms. All work is done in the context of some database, but to get to do the work, one needs to first have access to the server and then to have access to the database.

Access to the server is granted via logins. There are two main categories of logins: SQL Server authenticated logins and Windows authenticated logins. I will usually refer to these using the shorter names of SQL logins and Windows logins. Windows authenticated logins can either be logins mapped to Windows users or logins mapped to Windows groups. So, to be able to connect to the server, one must have access via one of these types or logins - logins provide access to the server realm.

But logins are not enough, because work is usually done in a database and databases are separate realms. Access to databases is granted via users.

Users are mapped to logins and the mapping is expressed by the SID property of logins and users. A login maps to a user in a database if their SID values are identical. Depending on the type of login, we can therefore have a categorization of users that mimics the above categorization for logins; so, we have SQL users and Windows users and the latter category consists of users mapped to Windows user logins and of users mapped to Windows group logins.

Let's take a step back for a quick overview: a login provides access to the server and to further get access to a database, a user mapped to the login must exist in the database.

that's the link to the full post.

In Short,

Logins will have the access of the server.

and

Users will have the access of the database.