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.

1. 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.
2. 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.