Modifying Azure Synapse Dedicated SQL Pool Permissions: Downgrading from Owner

Ahwan Mishra 120 Reputation points
2024-02-07T21:19:11+00:00

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?

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,140 questions
{count} votes

Accepted answer
  1. phemanth 3,755 Reputation points Microsoft Vendor
    2024-02-08T09:18:50.6466667+00:00

    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.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful