Role access to Edit Azure Server firewall rules via TSQL

Nandan Hegde 29,886 Reputation points MVP
2021-07-09T06:28:25.053+00:00

Hello All,
I have an AD group provided as DBmanager and Loginmanager roles at Azure Server level (meaning master database)

When I execute the below query :

EXECUTE sp_delete_firewall_rule @DeezNutz = N'<<Name>>'

I get the below error:
Msg 15247, Level 16, State 101, Procedure sp_delete_firewall_rule, Line 1 [Batch Start Line 2]
User does not have permission to perform this action.

So what permission do I need to give to be able to execute that task? Is that even possible ?

Note: I cannot add that group in server AD admin as another user group has that.
I need to somehow reciprocate the access to the new DL

Azure SQL Database
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,367 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Oury Ba-MSFT 16,081 Reputation points Microsoft Employee
    2021-07-09T19:34:29.66+00:00

    Hi @Nandan Hegde Thank you for posting your question on Microsoft Q&A. Only the server-level principal login created by the provisioning process can delete server level firewall rules. The user must be connected to the master database to execute sp_delete_firewall_rule.

    To be able to create and manage IP firewall rules for the Azure SQL Server, you will need to either be:

    1. In the SQL Server Contributor role
    2. In the SQL Security Manager role
    3. The owner of the resource that contains the Azure SQL Server

    https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-delete-firewall-rule-azure-sql-database?view=azure-sqldw-latest

    Regards,
    Oury