为何授予Windows认证用户SQL权限时自动创建用户架构？

问题描述 #

我需要给一名Windows认证用户授予权限，该用户此前仅拥有默认的public服务器角色，没有任何用户映射或安全对象。在master数据库中执行了以下SQL代码后，权限确实正确设置了，但同时自动创建了一个和用户名同名的用户架构，我搞不清原因，查资料也没找到明确解释，想问问这是不是由sp_addrolemember触发的？

执行的SQL代码：

--Database Context
USE [master]
--Role Memberships
EXEC sp_addrolemember @rolename=[db_datareader],@membername=[Username]
EXEC sp_addrolemember @rolename=[db_datawriter],@membername=[Username]
--Database Level Permissions
GRANT CONNECT TO [Username]
--Set default schema
ALTER USER [Username] WITH default_schema = dbo

原因解析 #

没错，这个自动创建的架构确实和sp_addrolemember的执行直接相关，具体逻辑是这样的：

• 你提到该用户之前没有在master数据库的用户映射，也就是说master库中原本不存在对应的数据库用户。而sp_addrolemember的作用是把用户添加到数据库角色，前提是该用户必须是当前数据库的合法用户——所以SQL Server会在执行这个存储过程时隐式创建一个与Windows登录名对应的数据库用户。
• 在隐式创建数据库用户的过程中，如果你没有提前指定默认架构，SQL Server会遵循内置的默认行为：自动创建一个与用户名完全同名的架构，并将这个架构设为该用户的默认架构。
• 你最后执行的ALTER USER [Username] WITH default_schema = dbo只是把用户的默认架构改成了dbo，但之前隐式创建用户时生成的同名架构已经存在了，并不会被覆盖或删除。

补充一个小建议：如果想要避免这种自动生成的同名架构，建议先显式创建数据库用户并指定默认架构，再执行角色添加操作，示例代码如下：

USE [master]
CREATE USER [Username] FOR LOGIN [Username] WITH DEFAULT_SCHEMA = dbo
EXEC sp_addrolemember @rolename=[db_datareader],@membername=[Username]
EXEC sp_addrolemember @rolename=[db_datawriter],@membername=[Username]
GRANT CONNECT TO [Username]

内容的提问来源于stack exchange，提问作者Ben M