Second member of Azure AD group can not create schema in Azure SQL database-CodePudding

We have a curious issue with our Azure SQL database with AAD authentication.

We have created a role (dbt_user) with all permissions, and excluded some schemas:

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;

Then, we add an AAD group (myAADGroup) as user, and add this to the role above:

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

There are 2 members of the AAD group: [email protected] and [email protected].

The first user can log in, and execute the following command:

CREATE SCHEMA [test_schema_user1];

However, when user2 executes the following command, we get an error:

CREATE SCHEMA [test_schema_user2];

Msg 2760, Level 16, State 1, Line 1
The specified schema name "[email protected]" 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.

Only the first user can successfully create a schema.

I only found 2 slightly related issues online, but they talk about CREATE TABLE, which can be circumvented by explicitly defining dbo as schema prefix for the table:

Microsoft Tech Community
Azure docs (see the note at the end of step 5)

Some things I executed as [email protected] in order to debug:

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?

Thanks!

CodePudding user response：

Assuming you have AAD admin rights,

When user1 a member of the myAADGroup group, logs in and tries to create a new SCHEMA. A new schema will be created in the database. If any other member within this group, say user2 tries to CREATE SCHEMA, an Invalid Object Name error will be shown. Also, default_schema_name is Null for principals not of type SQL user, Windows user, or Application role.

This security issue is resolved by assigning a default schema for Groups.

USE your_database;
GO

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

ALTER USER [myAADGroup] WITH DEFAULT_SCHEMA=[dbo]

However, please see the following note from the Remarks section of the CREATE SCHEMA documentation:

Note: The implicit creation of an Azure Active Directory user is not possible on SQL Database. Since creating an Azure AD user from external provider must check the users status in the AAD, creating the user will fail with error 2760: The specified schema name "<user_name@domain>" either does not exist or you do not have permission to use it. And then error 2759: CREATE SCHEMA failed due to previous errors. To work around these errors, create the Azure AD user from external provider first and then rerun the statement creating the object.