In an Azure Synapse dedicated SQL pool I have the following setup:
-- a custom DB role to manage privileges CREATE ROLE [owner]; -- there is a schema owned by this role CREATE SCHEMA [myschema] AUTHORIZATION [owner]; -- an Azure AD group to allow its members to log in CREATE USER [radish] FROM EXTERNAL PROVIDER; -- the AAD group is a member of the owner role EXEC sp_addrolemember 'owner', 'radish'; -- privileges are assigned exclusively through custom DB roles GRANT ALTER, CONTROL on SCHEMA::[myschema] TO [owner];
If I log in, being a member of the
radish AAD group, I cannot create a table in
CREATE TABLE [myschema].[test] (id int); Msg 6004, Level 14, State 9, Line 1 User does not have permission to perform this action.
Once I get the Synapse Administrator role, this works. (When a member,
dbo.) This is no surprise, as this role can do basically everything inside the SQL pools of the given workspace.
However, when I remove myself from the Synapse Administrator role, the following happens:
DROP TABLE [myschema].[test]; -- completes successfully, but then: CREATE TABLE [myschema].[test] (id int); Msg 6004, Level 14, State 9, Line 1 User does not have permission to perform this action.
Probably my understanding about how the necessary privileges for
CREATE TABLE and
DROP TABLE relate to one another is totally wrong, but until now I thought it were the same privileges needed for both. Can someone please show me where my thinking is wrong?
So far I see the following possibilities:
ownerrole doesn't apply to users that are only indirectly member of it (i.e. through membership in the AAD group) - this seems to contradict https://learn.microsoft.com/en-us/azure/synapse-analytics/security/how-to-set-up-access-control#step-8-add-users-to-security-groups
- the table created when having enough privileges is owned by myself and not the schema owner role. The 'Caution' section contradicts: 'A user with ALTER permission on a schema can create procedures, synonyms, and views that are owned by the schema's owner.' Also,
Also, interestingly, I can do a
GRANT ALTER, CONTROL on SCHEMA::[myschema] TO [owner]; successfully, whereas creating the table fails.