Modifying Azure Synapse Dedicated SQL Pool Permissions: Downgrading from Owner

Question

Hi everyone, I'm looking to adjust permissions in Azure Synapse Dedicated SQL Pool, specifically to downgrade from owner to a lower level permission set. I still need the ability to Read, Write, Alter objects, Create schemas with dbo authentication, and Alter Schemas with dbo as authentication. Can someone guide me on how to achieve this effectively?

Answer 1

Hi @Ahwan Mishra
Thank you for posting query in Microsoft Q&A Platform.

Adjusting permissions in Azure Synapse Dedicated SQL Pool involves a combination of roles, permissions, and access control mechanisms. Let’s break it down step by step:

1. Security Groups:
   • Start by setting up security groups to group users with similar access requirements. This simplifies access management by allowing you to add or remove users from these groups.
   • Align roles and personas in your organization with these security groups.
2. Azure Roles:
   • Use Azure roles to control access to various resources:
   • Synapse SQL Pools: These roles determine who can create and manage SQL pools.
   • Apache Spark Pools: These roles control access to Spark compute resources.
   • Integration Runtimes: These roles manage access to integration runtimes.
   • ADLS Gen2 Storage: Configure roles to control access to storage.
3. Synapse Roles:
   • Synapse roles provide finer-grained control within the workspace:
   • Published Code Artifacts: Control access to code artifacts.
   • Apache Spark Compute Resources: Manage Spark-related permissions.
   • Integration Runtimes: Define access to integration runtimes.
4. SQL Permissions: To manage tables and data, consider the following:

• To drop tables or views, users need ownership permissions on the object. You can either:
• Alter the authorization on the table: ALTER AUTHORIZATION ON YourSchema.YourTable TO YourUserOrRole.
• Grant the CONTROL permission at the table level: GRANT CONTROL ON OBJECT::YourSchema.YourTable TO YourUserOrRole. Git Permissions (if using Git for source control):
  • Control who can access code artifacts in source control.

Remember to replace the standard names (e.g., workspace1, storage1, etc.) with your actual resource names. For more details, refer to the official Microsoft Learn guide on setting up access control for Azure Synapse workspaces

https://learn.microsoft.com/en-us/sql/t-sql/statements/permissions-grant-deny-revoke-azure-sql-data-warehouse-parallel-data-warehouse?view=aps-pdw-2016-au7

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.