Truncate Table permission in Azure Synapse

Question

Hi,

We have several schemas inside a dedicated sqlpool. I want to give Truncate privilege to all the objects of a particular schema to a AAD group.

I found the below syntax:

EXEC sp_addrolemember 'db_ddladmin', 'Azure AD Group';

But it seems that it will provide that privilege across all other schemas as well. How can i restrict to a particular schema.

Answer 1

you can use DCL rather than the sys proc

GRANT ALTER on schema::schemaname to 'Azure AD Group'