Difference between a User and a Login in SQL Server

I have recently been running into many different areas of SQL Server that I normally don’t mess with. One of them that has me confused is the area of Logins and Users. Seems like it should be a pretty simple topic…

It appears that each login can only have 1 user and each user can only have 1 login.

  • Is it better to have SQL Server parse a large multi document XML or send it each document separately
  • SqlServers Engine
  • Optimizing specific sql query
  • Android App With Server Interaction
  • How to get the difference between two datetime columns in SQL
  • find number of open connection on database
  • A login can be associated to multiple tables thus associating that user to many tables.

    So my question is why even have a login and a user? they seem to be pretty much one in the same. What are the differences, or what is it that I seem to be missing?

    5 Solutions collect form web for “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

    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.

    In Short,

    Logins will have the access of the server.

    and

    Users will have the access of the database.

    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.

    I think this is a very useful question with good answer. Just to add my two cents from the MSDN Create a Login page:

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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.