question

NandanHegde-7720 avatar image
0 Votes"
NandanHegde-7720 asked OuryBa-MSFT answered

Role access to Edit Azure Server firewall rules via TSQL

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 @name = 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-databaseazure-synapse-analytics
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

OuryBa-MSFT avatar image
0 Votes"
OuryBa-MSFT answered

Hi @NandanHegde-7720 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://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-delete-firewall-rule-azure-sql-database?view=azure-sqldw-latest

Regards,
Oury



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @NandanHegde-7720 I haven't heard back from you and would like to check with you if the above answer was helpful. Please let us know

Regards,
Oury

0 Votes 0 ·

Hi @NandanHegde-7720 I haven't heard back from you and would like to check with you if the above answer was helpful. Please let us know

Regards,
Oury

0 Votes 0 ·