Role access to Edit Azure Server firewall rules via TSQL

Question

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

Answer 1

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