I'm setting up my database on an Azure SQL Server instance.
(I know the principal names are terrible, it's just a dry run test)
I created a login ("Test"), a user ("Test"), a schema ("Test"), added a table to the schema ("TestTable", created a role ("TestWriter"), set role permissions for the schema and added the user to the role.
I granted the role the following permissions:
GRANT INSERT, SELECT, UPDATE ON SCHEMA::Test TO TestWriter GO
and denied all other permissions, following the practice of "least permissions" (i.e. giving a role/user only the minimum permissions it needs):
DENY DELETE, ALTER, CONTROL, EXECUTE, REFERENCES, TAKE OWNERSHIP, VIEW DEFINITION ON SCHEMA::Test TO TestWriter GO
however then there was an error selecting and/or inserting into the table in the schema, the error being
The INSERT permission was denied on the object 'TestTable', database 'TestDb', schema 'Test'.
through trial and error I found out the problem is fixed if I
CONTROL permission to the role.
However, from what I read the
CONTROL permission isn't something I'd want to grant willy-nilly to anyone who just needs to SELECT/INSERT into a table. Is there something I am missing? Is it possible to SELECT/INSERT/UPDATE a table WITHOUT having