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: user1@domain.com
and user2@domain.com
.
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 "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.
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 user2@domain.com 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!
Advertisement
Answer
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;
or
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.