Is there a "Reader" Access Control Role in Synapse Analytics Studio for connecting to a Serverless SQL Endpoint?

WC 1 Reputation point
2023-01-04T21:47:39.803+00:00

When using SQL Server Management Studio or creating a dataflow connection in PowerBI online, we've found that we must give an account the "Synapse SQL Administrator" role in order to properly connect without credential or login errors. We're wondering if there's a lower permission role like a "reader" instead of of an "admin" role that we give for access instead.

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.
2,627 questions
No comments
{count} votes

1 answer

Sort by: Most helpful
  1. WC 1 Reputation point
    2023-01-05T16:14:11.657+00:00

    Answered my own question...
    From: https://learn.microsoft.com/en-us/azure/synapse-analytics/security/how-to-set-up-access-control#configure-database-scoped-permissions

    Synapse SQL Administrator gives db_datareader and db_datawriter on serverless pool databases. Instead, customer can create a user in synapse serverless and assign only db_datareader. This user/account does not need RBAC in Synapse Studio, but needs Storage Account Reader RBAC

    Configure Database-scoped permissions
    You can grant users access to a single serverless SQL database with the steps outlined in this example:

    Create a login. Change to the master database context.

    SQL

    Copy
    --In the master database
    CREATE LOGIN [alias@keyman .com] FROM EXTERNAL PROVIDER;
    Create user in your database. Change context to your database.

    SQL

    Copy
    -- In your database
    CREATE USER alias FROM LOGIN [alias@keyman .com];
    Add user as a member of the specified role in your database (in this case, the db_owner role).

    SQL

    Copy
    ALTER ROLE db_owner ADD member alias; -- Type USER name from step 2