Friday, December 23, 2016

Delete All Azure SQL Server Firewall Rules using TSQL

In order to access Azure SQL Server databases, firewall rules have to be set at either server level or database level by adding client IP addresses. Server level rules can be configured using the Azure Portal but database level rules have to be done using TSQL. If you have many added many rules and you need to delete all or some, and if you try to use the portal for deleting, it will take long time because portal allows you to delete one at a time.

Assume that you have many number of server level rules configured as below;

I was asked about this and here is the script I wrote to delete selected server level firewall rules.

DECLARE @name nvarchar(200)
WHILE EXISTS (SELECT name FROM sys.firewall_rules WHERE name LIKE 'clientIPAddress_%')

 SELECT TOP (1) @name = name FROM sys.firewall_rules WHERE name LIKE 'clientIPAddress_%'
 EXEC sp_delete_firewall_rule @name;

It is a simple one but you can use it without using the portal. Make sure you have written the WHERE Condition properly otherwise it might delete rules you may need.

No comments: