Question 103614 in Azure Question posted in
The official documentation to get you started can be found here.

What's the minimum role a SQL user needs to export a bacpac in Azure?

We’re exporting bacpacs from Azure SQL Databases to Storage Accounts via New-AzSqlDatabaseExport. Docs for that command: https://learn.microsoft.com/en-us/powershell/module/az.sql/new-azsqldatabaseexport

What’s the minimum role required for a SQL user to do this? We’re trying to maintain the least privileges rule.

Having a single SQL user that can export bacpacs for all databases in a server is acceptable.

Tags:

2

Answers


  1. 0

    It should be db_backupoperator:

    Members of the db_backupoperator fixed database role can back up the
    database.

    Note, that db_backupoperator roles is not applicable in Azure SQL database (not managed instance) and Azure Synapse serverless pool because backup and restore T-SQL commands are not available.

    The user needs to be member of dbmanager:

    Can create and delete databases. A member of the dbmanager role that
    creates a database, becomes the owner of that database, which allows
    that user to connect to that database as the dbo user. The dbo user
    has all database permissions in the database. Members of the dbmanager
    role don’t necessarily have permission to access databases that they
    don’t own.

    Login or Signup to reply.
  2. 0

    What’s the minimum role required for a SQL user to do this? We’re
    trying to maintain the least privileges rule. Having a single SQL user
    that can export bacpacs for all databases in a server is acceptable.

    To perform any task in the Azure storage account, You’re required to assign at least Storage Blob Data Contributor or Storage Blob Data Owner role.

    If you’re using SQL authentication with a local username and password, You can assign a User account with which you are logging into Azure Portal and accessing Azure SQL database with the storage blob data contributor role and SQL Server/DB contributor role to access the storage account and Azure SQL server. But the same Azure user account cannot access the SQL Server, Just perform management tasks. You can also use the same Azure user account to log into your Azure SQL database by adding it as an Azure AD SQL Admin. This will maintain a single user that can access both your Azure SQL and storage account.

    I created one Azure AD user and assigned the following roles like below:-

    enter image description here

    Storage Blob data contributor role:- Allows managing storage containers and data and accessing the storage keys and performing storage account level tasks. [Assigned at Resource group level where our SQL and storage account exists.]

    SQL DB Contributor role:- Allows to perform Azure SQL-related tasks but does not allow access to SQL DB. [Assigned at Resource group level where our SQL and storage account exists.]

    Storage Account Contributor role:- Allows managing Storage account and its associated resources. [Assigned at Resource group level where our SQL and storage account exists.]

    Note- I have added the Storage account Contributor role at the Subscription level just to allow the user to create a PowerShell
    storage account in Azure cloud shell to run the export command. If you have your storage account and Azure SQL server in the same resource group as your cloud shell storage account, You can assign this role at that resource group level like above.

    I tried the below command to export Azure SQL DB with siliconSQL user and it worked successfully like below:-

    PowerShell Script:-

    $SecurePassword=ConvertTo-SecureString xxxxxmelon@123 –asplaintext –force
New-AzSqlDatabaseExport -ResourceGroupName "siliconrg" -ServerName "azsqlservername" -DatabaseName "azsqlDBname" -StorageKeyType "StorageAccessKey" -StorageKey "xxxxxxxxxxxxxxxxxxxxxxxxxxxxx<storage access key>dnzjSrjIC3k1IbT7ozsSw0WVGo+AStiPXEXA==" -StorageUri "https://siliconstrg123.blob.core.windows.net/bacpacs/database01.bacpac" -AdministratorLogin "siliconuser" -AdministratorLoginPassword $SecurePassword

    Output:-

    enter image description here

    Alternatively, You can also add this user as Azure AD SQL admin to access the SQL server like below:-

    enter image description here

    Reference:-

    Role required for bacpac import? · Issue #65893 · MicrosoftDocs/azure-docs · GitHub

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search