there are number of test IP's which I would like to remove through system defined sp
exec sp_delete_firewall_rule from sys.firewall_rules table in sql server
I am using below cursor but its not working
declare @name nvarchar(max)
declare cur CURSOR LOCAL for
select @name from sys.firewall_rules where [name] like '%TestIP%'
open cur
fetch next from cur into @name
while @@FETCH_STATUS = 0 BEGIN
exec sp_delete_firewall_rule @name
fetch next from cur into @name
END
close cur
deallocate cur
CodePudding user response:
It worked for me, you just need to change a couple of things in your code.
- In the select list include the table ColumnName [name] instead of variable. You did not pass any value to the variable so this gives a NULL result.
- Include SP parameter while executing
exec sp_delete_firewall_rule @name = @name1;
I have these IP’s in my firewall rules:
With the below code I am deleting the IP’s which has a name like TestIP1.
DECLARE @name1 nvarchar(128);
DECLARE MyCursor CURSOR FOR
SELECT [name] from sys.firewall_rules where [name] like '%TestIP1%';
OPEN MyCursor;
FETCH FROM MyCursor into @name1
WHILE @@FETCH_STATUS = 0 BEGIN
EXEC sp_delete_firewall_rule @name = @name1 ;
FETCH next from MyCursor into @name1
END
CLOSE MyCursor;
DEALLOCATE MyCursor;
GO
Now the result shows only 1 IP which is not included in the above delete list.