Azure AD组的第二个成员无法在Azure SQL数据库中创建架构

我们的Azure SQL数据库使用AAD身份验证时有一个奇怪的问题。

我们已经创建了一个具有所有权限的角色(dbt_user)，并排除了一些架构：

CREATE ROLE dbt_user AUTHORIZATION dbo;

GRANT CREATE SCHEMA, ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, UPDATE TO dbt_user;
DENY ALTER, DELETE, EXECUTE, INSERT, UPDATE ON SCHEMA :: dbo TO dbt_user;
DENY ALTER, DELETE, EXECUTE, INSERT, UPDATE ON SCHEMA :: schema1 TO dbt_user;
DENY ALTER, DELETE, EXECUTE, INSERT, UPDATE ON SCHEMA :: schema2 TO dbt_user;

然后，我们添加一个AAD组(myAADGroup)作为用户，并将其添加到上面的角色中：

CREATE USER [myAADGroup] FROM EXTERNAL PROVIDER;
ALTER ROLE dbt_user ADD MEMBER [myAADGroup];

AAD组有2个成员：user1@domain.com和user2@domain.com。

第一个用户可以登录，执行以下命令：

CREATE SCHEMA [test_schema_user1];

但是，当user2执行以下命令时，我们收到错误：

CREATE SCHEMA [test_schema_user2];

Msg 2760, Level 16, State 1, Line 1
The specified schema name "user2@domain.com" either does not exist or you do not have permission to use it.
Msg 2759, Level 16, State 0, Line 1
CREATE SCHEMA failed due to previous errors.

只有第一个用户才能成功创建架构。

我在网上只发现了两个稍微相关的问题，但它们都谈到了CREATE TABLE，可以通过将dbo显式定义为表的模式前缀来规避这些问题：

• Microsoft Tech Community
• Azure docs(参见步骤5末尾的注释)

为了调试，我以user2@domain.com身份执行了一些操作：

select SCHEMA_NAME()
-- returns dbo

SELECT name, default_schema_name
FROM sys.database_principals
WHERE name = 'myAADGroup'
-- default_schema_name = dbo

SELECT name, default_schema_name
FROM sys.database_principals
WHERE name = 'dbt_user'
-- default_schema_name = null  --> a role can not have a default_schema?

谢谢！

---

解决方案

假设您拥有AAD管理员权限，

当user1是myAADGroup组的成员时，登录并尝试创建新架构。将在数据库中创建一个新架构。如果此组中的任何其他成员(例如user2)尝试创建架构，则将显示无效对象名错误。此外，对于非SQL用户、Windows用户或应用程序角色类型的主体，default_schema_name为Null。

此安全问题可通过为组分配默认架构来解决。

USE your_database;
GO

CREATE USER [myAADGroup] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = dbo;

或

ALTER USER [myAADGroup] WITH DEFAULT_SCHEMA=[dbo]

但是，请参阅Remarks section of the CREATE SCHEMA documentation中的以下说明：

注意：无法在SQL数据库上隐式创建Azure Active Directory用户。由于从外部提供程序创建Azure AD用户必须检查AAD中的用户状态，因此创建用户将失败，并返回错误2760：指定的架构名称&USER_NAME@DOMAIN&>不存在或您没有使用该名称的权限。然后出现错误2759：CREATE SCHEMA因以前的错误而失败。若要解决这些错误，请首先从外部提供程序创建Azure AD用户，然后重新运行该语句