Difference between a User and a Login in SQL Server

Question

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

User · Answer

Graph on logins   users from MS sql-docs

User · Answer

In Short   Logins will have the access of the server   and  Users will have the access of the database

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Difference between a User and a Login in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005