I need to grant a user certain permissions onto to their own schema on Azure Dedicated Pool, however I'm struggling with GRANT CREATE TABLE which just doesn't seem to work unless I also grant ALTER which I don't want to do as that allows them to DROP objects.
I feel like I'm missing something, but for the life of me, I cannot see what, and all the digging I've done says GRANT CREATE TABLE should just work.
The permissions I've granted are:
GRANT SELECT ON SCHEMA::[myschema] TO [Robin];
GRANT CREATE TABLE TO [Robin];
GRANT DELETE ON SCHEMA::[myschema] TO [Robin];
GRANT INSERT ON SCHEMA::[myschema] TO [Robin];
GRANT UPDATE ON SCHEMA::[myschema] TO [Robin];
I have confirmed all permissions were set as well.
I'm able to SELECT/INSERT/UPDATE/DELETE just fine, but when I try to create a new table
CREATE TABLE myschema.TESTTABLE
(
COL1 INT
,COL2 VARCHAR(10)
,COL3 DATETIME
) WITH(DISTRIBUTION=ROUND_ROBIN,HEAP);
, I get this error:
Msg 6004, Level 14, State 9, Line 14
User does not have permission to perform this action.
Changed database context to 'mydatabasename'.