good morning
does anyone have any idea about what's wrong with my privilege setup (in Synapse, but probably it's the same in plain SQL Server as well), when:
- there is a schema owned by a custom DB role
- there is an AD group being a member of this role
- another AD user is a member of the aforementioned AD group
- this user can drop a table in the schema, but cannot create it again
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 ...
