SQL Server中的login和database user

SQL Server的用户体系和MySQL有非常大的不同，无论是在使用上、还是概念上。所以，这里梳理一下SQL Server的用户与认证的一些基础概念与使用。另外，这个概念在SQL Server相关资料中各个地方都会出现，是理解权限体系的基础。

“login”与“database user”

在SQL Server中，”login”不是一个动词，而是一个”名称”（注：”log in”是动词），代表的是一个用于登录的对象（Object），这是一个服务器级别的对象，所以，它有自己的登录名(login name)、密码、默认语言、认证方式等。需要强调的是，它不是一个数据库（database）级别的对象。

而”Database User”是一个数据库级别的对象，与之相对应的则是数据库级别的权限。”Database User”并不能连接或者登录SQL Server实例，所以，一般来说，也不需要密码。

“login”因为是Server级别的，所以权限也都是Server级别的。本身不能赋予任何数据库相关的权限，但是，login可以和一个Database User建立映射，使用该login连接数据库的时候，该连接也就可以根据Database User权限进行相关的操作了。

最常见、最简单的创建login和database user的命令如下：

  use zzxdb2;
  create table t_1(id int,nick nchar(12),birthday date);

  create login zzxdb2 with password='zzxdb2' ,CHECK_POLICY=OFF;
  create user zzxdb2 for login zzxdb2;

  -- 当没有赋予权限的时候，zzxdb2可以登录SQL Server，但是看不到zzxdb2下面的TABLE
  -- 所以，最后还需要赋予database user相应的权限，如下
  exec sp_addrolemember 'db_owner', 'zzxdb2';

为什么容易混淆

通常的系统中只有用户的概念，权限系统都是基于用户。而SQL Server在其上新增了Login这一层，与其他的系统都不同。另外，在一般的客户端中，在需要登录的时候，通常都是使用”user name”/”password”作为登录认证的凭证，而不是”Login”/”password”，所以初学者通常容易混淆，例如微软的Mac客户端Azure Data Studio：

一些可以帮助理解”login”和”user”关系的一些问题

1. 只创建login，而不map到一个具体的database用户，是否可以登录？

答案是简单的：可以登录，但是没有数据库相关的权限。测试如下：

先创建一个没有映射到”user”的”login”：

CREATE LOGIN alogin WITH PASSWORD = 'alogin', CHECK_POLICY=OFF;
-- 注： CHECK_POLICY可以让你设置简单密码，并不建议加上

使用上面的”login”在Azure Data Studio中连接并进入SQL Server。可以看到，可以正常登录，但是因为没有database相关的权限，所以展示database里面的对象的时候，会失败，如下：

也就说，”login”只有在与具体的某个database user建立了mapping之后，才可以访问对应的数据库。在上面例子中的”login”主体”alogin”，要访问和管理数据库9zcloud，是会失败的。

当然，如果真的需要访问的话，那么我们需要先建一个database user，并在user和login之间建立mapping，具体操作如下：

CREATE USER a_db_user_9zcloud FOR LOGIN alogin;

2. 创建用户，不映射到任何login，后续是否还可以重新映射？

如果在用户创建的时候显式的声明，不映射到任何login，那么后续是否还可以重新映射到某个login？答案似乎没有那么明显了。遂测试如下：

CREATE USER a_db_user_9zcloud WITHOUT LOGIN;
ALTER USER  a_db_user_9zcloud WITH LOGIN='alogin';
-- 报错如下：

消息 33016，级别 16，状态 1，第 45 行
The user cannot be remapped to a login. Remapping can only be done for users that were mapped to Windows or SQL logins.

可见，如果在创建的时候显示声明不映射到任何”login”，那么就不能够再重新映射任何的”login”。

3. 如果用户名和login主体名字不一样，客户端登录的时候使用哪个？

答案是显然的，但是还是验证一下。

具体的，如果数据库用户名和login用户名不一样，那么在登录连接SQL Server的时候，使用的是database user还是login的名称？具体看下面的例子，在使用客户端登录的时候，使用的alogin，还是使用a_db_user_9zcloud？

CREATE LOGIN alogin WITH PASSWORD = 'alogin', CHECK_POLICY=OFF;
CREATE USER a_db_user_9zcloud FOR LOGIN alogin;

答案，当然是使用login的主体alogin。

4. 在创建用户时如果映射到了某个login，同时也创建密码，那么这个密码有什么用？

是啊，有什么用呢？具体的，在创建用户时映射到某个具体的login，但是依旧指定一个密码，那么这个密码有什么用？测试验证如下：

CREATE LOGIN alogin WITH PASSWORD = 'alogin', CHECK_POLICY=OFF;
CREATE USER a_db_user_9zcloud FOR LOGIN alogin WITH PASSWORD = 'dbuser9zcloud';

答案：你就不能这么用！！（注：仅当在contained database中可以使用密码，参考）在明确映射到某个具体的login的用户，不需要密码，也无法指定密码。所以，上面的语句执行会失败，报如下错误：

消息 33234，级别 16，状态 1，第 47 行

The parameter PASSWORD cannot be provided for users that cannot authenticate in a database.

另外，注意到login在创建的时候，是可以指定默认数据库(DEFAULT_DATABASE)的；创建用户的时候，可以指定默认的schema。

其他内容

在给一个对象(主体)赋权的时候，可以通过按照细粒度(某个表的某种权限)方式进行，也可以直接将其加入到某个角色组，那么这个角色组对应的权限就都有了。例如，将login加入到”sysadmin”(fixed server role)，那么就有了所有sysadmin角色组的权限，sysadmin可以理解是一个超级权限组，如果在该组中，那么访问对象时不需要检查该账号的权限；与sysadmin对应的一个权限是”CONTROL SERVER”，如果使用GRANT则可以使用这个权限。
另外，前文中偶尔会用到”主体”这个名称，英文对应SQL Server文档中的”Principals”，”主体”是SQL Server官方中文文档的翻译(参考)。可以理解为一个实体，或者前面对象的实例化或者实体，也就是说，某个具体的”server roles, logins, database roles, or users.” 都可以称作”Principals”。
SQL Server中的系统表sys.server_principals、sys.server_permissions会存储相关的元数据。
在SQL Server官方文档中将”login”翻译为”登录名”(参考)。这也是为什么，一些客户端在让输入用户名的时候，其实是输入一个login主体名和对应的密码。
在创建user的时候，如果没有显示的指定FOR LOGIN，没有指定WITHOUT LOGIN，那么该user将会被映射到同名的login上(还没有验证这一点，参考：If FOR LOGIN is omitted, the new database user will be mapped to the SQL Server login with the same name.)。
另外，系统中还有一个名字为guest的login，默认是不可用的。
在实际中，也有一些场景是需要创建user，而不映射到任何的login，后续会再考虑介绍这类场景。

参考阅读

Determining Effective Database Engine Permissions：链接如何查看系统中的账号权限
Database Engine Permissions SQL Server 2017 and Azure SQL Database：链接

orczhou.com