SQL Server 中用户和登录名的区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1134319/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Difference between a User and a Login in SQL Server
提问by corymathews
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...
我最近遇到了 SQL Server 的许多不同领域，我通常不会弄乱这些领域。其中一个让我感到困惑的是登录和用户区域。看起来它应该是一个非常简单的主题......
It appears that each login can only have 1 user and each user can only have 1 login.
看来每次登录只能有1个用户，每个用户只能有1个登录。
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?
所以我的问题是为什么甚至有一个登录名和一个用户？他们似乎几乎是一回事。有什么区别，或者我似乎缺少什么？
回答by Scott Ivey
A "Login" grants the principal entry into the SERVER.
“登录”授予主体进入 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
上述每个对象都可以在其自己的级别授予其权限。有关每个的解释，请参阅以下文章
回答by Tom Resing
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.
两者兼而有之的一个原因是，身份验证可以由数据库服务器完成，但授权范围可以限定在数据库内。这样，如果您将数据库移动到另一台服务器，您始终可以重新映射数据库服务器上的用户-登录关系，但您的数据库不必更改。
回答by David Leitner
I think there is a really good MSDN blog postabout this topic by Laurentiu Cristofor:
我认为Laurentiu Cristofor 有一篇关于这个主题的非常好的MSDN 博客文章：
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 accessto the server and to further get access to a database, a user mappedto the login must exist in the database.
关于 SQL Server 安全性需要了解的第一件重要事情是涉及两个安全领域——服务器和数据库。服务器领域包含多个数据库领域。所有工作都是在某个数据库的上下文中完成的，但是要开始工作，首先需要访问服务器，然后才能访问数据库。
通过登录授予对服务器的访问权限。登录有两大类：SQL Server 验证登录和 Windows 验证登录。我通常会使用 SQL 登录名和 Windows 登录名的较短名称来引用这些。Windows 验证登录可以是映射到 Windows 用户的登录，也可以是映射到 Windows 组的登录。因此，为了能够连接到服务器，必须通过这些类型或登录之一进行访问 - 登录提供对服务器领域的访问。
但登录是不够的，因为工作通常在数据库中完成，而数据库是不同的领域。通过用户授予对数据库的访问权限。
用户映射到登录名，映射由登录名和用户的 SID 属性表示。如果用户的 SID 值相同，则登录映射到数据库中的用户。因此，根据登录类型，我们可以对用户进行分类，以模拟上述登录分类；因此，我们有 SQL 用户和 Windows 用户，后者由映射到 Windows 用户登录的用户和映射到 Windows 组登录的用户组成。
让我们退后一步快速概览：登录提供对服务器的访问，并进一步访问数据库，映射到登录的用户必须存在于数据库中。
that's the linkto the full post.
回答by Vikrant Kedari
In Short,
简而言之，
Loginswill have the access of the server.
登录将具有服务器的访问权限。
and
和
Userswill have the access of the database.
用户将有权访问数据库。
回答by ilmatte
I think this is a very useful question with good answer. Just to add my two cents from the MSDN Create a Loginpage:
我认为这是一个非常有用的问题，答案很好。只是为了从 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:
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.
登录名是一个安全主体，或者是一个可以通过安全系统进行身份验证的实体。用户需要登录才能连接到 SQL Server。您可以创建基于 Windows 主体（例如域用户或 Windows 域组）的登录名，也可以创建不基于 Windows 主体的登录名（例如 SQL Server 登录名）。
注意：
要使用 SQL Server 身份验证，数据库引擎必须使用混合模式身份验证。有关详细信息，请参阅选择身份验证模式。作为安全主体，可以授予登录权限。登录的范围是整个数据库引擎。要连接到 SQL Server 实例上的特定数据库，必须将登录名映射到数据库用户。数据库内的权限被授予和拒绝给数据库用户，而不是登录名。可以向登录授予具有整个 SQL Server 实例范围的权限（例如，CREATE ENDPOINT 权限）。
