用户与SQL服务器中的登录名之间的差异

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.

每个登录名只能有一个用户，每个用户只能有一个登录名。

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 个解决方案

#1

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
• 数据库用户

#2

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.

两者兼备的一个原因是，身份验证可以由数据库服务器完成，但授权可以限定在数据库的范围内。这样，如果您将数据库移动到另一个服务器，您总是可以重新映射数据库服务器上的用户登录关系，但是您的数据库不需要更改。

#3

In Short,

简而言之,

Logins will have the access of the server.

登录将获得服务器的访问权限。

and

和

Users will have the access of the database.

用户可以访问数据库。

#4

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

我认为有一个很好的MSDN博客文章关于这个话题，作者是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.

关于SQL Server安全性，需要理解的第一件重要的事情是涉及到两个安全领域——服务器和数据库。服务器领域包含多个数据库领域。所有工作都是在某个数据库的上下文中完成的，但是要完成这项工作，首先需要访问服务器，然后才能访问数据库。

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.

通过登录获得对服务器的访问。有两种主要的登录类型:SQL Server身份验证登录和Windows身份验证登录。我通常使用更短的SQL登录名和Windows登录名来引用它们。Windows认证登录可以是映射到Windows用户的登录，也可以是映射到Windows组的登录。因此，要连接到服务器，必须通过这些类型之一或登录进行访问——登录提供对服务器领域的访问。

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.

用户被映射到登录，映射由登录者和用户的SID属性表示。如果用户的SID值相同，则登录将映射到数据库中的用户。因此，根据登录的类型，我们可以对用户进行分类，模仿上面的登录分类;我们有SQL用户和Windows用户，后者包括映射到Windows用户登录的用户和映射到Windows组登录的用户。

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.

这就是链接到全文的链接。

#5

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

我认为这是一个非常有用的问题，有很好的答案。只需要从MSDN中添加我的2美分创建一个登录页面:

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

登录是一个安全主体，或者可以通过安全系统进行身份验证的实体。用户需要登录才能连接到SQL Server。您可以基于Windows主体(如域用户或Windows域组)创建登录，也可以创建不基于Windows主体的登录(如SQL Server登录)。

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

注意:要使用SQL Server身份验证，数据库引擎必须使用混合模式身份验证。有关更多信息，请参见选择身份验证模式。

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实例上的特定数据库，必须将登录名映射到数据库用户。数据库中的权限被授予并拒绝给数据库用户，而不是登录。具有SQL Server整个实例范围的权限(例如，创建端点权限)可以授予登录。