Minimum permission to manage tables and data in a Synapse dedicated SQL pool

Question

Hi, I'd like to know which is the minimum permission in order to create/drop tables/views, read and write data in a Synapse dedicated SQL pool. Could it be enough belonging to the Synapse contributor role? Thanks

Answer 1

Hi @pmscorca

Welcome to Microsoft Q&A platform and thanks for posting your question here.

In additional to the Azar response, To create/drop tables/views, read and write data in a Synapse dedicated SQL pool, you need to have the db_owner role or db_ddladmin role in the SQL pool. The db_owner role provides full access to the database, including the ability to create, modify, and drop objects, while the db_ddladmin role provides the ability to create, modify, and drop objects, but not the ability to modify data.

The Contributor role in Synapse workspace provides access to manage resources in the workspace, but it doesn't provide access to the SQL pool. Therefore, you need to be assigned the db_owner or db_ddladmin role in the SQL pool to manage tables and data.

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

Hi pmscorca This seems like an question which has multiple roles wait let me show you how, These are fe of tthe minimum permissions required to create/drop tables/views and read/write. The CONTROL permission on the schema is required to create, drop, or alter tables and views.

sqlCopy code
GRANT CONTROL ON SCHEMA::YourSchema TO YourUserOrRole;

The ALTER permission on the schema is required to alter tables and views.

sqlCopy code
GRANT ALTER ON SCHEMA::YourSchema TO YourUserOrRole;

To read and write data, users need specific permissions on the tables. Grant SELECT, INSERT, UPDATE, and DELETE permissions as needed:

sqlCopy code
GRANT SELECT, INSERT, UPDATE, DELETE ON YourSchema.YourTable TO YourUserOrRole;

To drop tables or views, users need ownership permissions on the object:

sqlCopy code
ALTER AUTHORIZATION ON YourSchema.YourTable TO YourUserOrRole;

Alternatively, they can be granted the CONTROL permission at the table level:

sqlCopy code
GRANT CONTROL ON OBJECT::YourSchema.YourTable TO YourUserOrRole;

If this helps kindy access the answer thanks very much.