I have a desktop app that connects to SQL Azure database. Some users have dynamic IP addresses, and it is not possible to get them to use fixed addresses.

I've written an ASP.NET page hosted on the organisation's web server that allows users to validate their IP Address. The web server has a fixed IP address, and can read a user's IP.

I'm using EXECUTE sp_set_database_firewall_rule against a contained database from the ASP.NET page.

According to documentation this system stored procedure requires CONTROL permission on the database.

Ideally, I'd like the web page that allows a user to add a firewall rule for their IP address to allow adding a firewall rule, but nothing else. There don't appear to be any built in roles for this.

Is it possible to set up sufficient permission to add a firewall rule, but restrict other activities?

  • You might want to consider white listing a range of IP's too. That would be easier on the users and simpler to implement if it is workable.
    – Erik
    Mar 30, 2016 at 4:20
  • @Erik I'd already tried that approach, as apparently all the staff use the same ISP at home, but 1. ISPs aren't particularly forthcoming with their IP address allocations, 2. they're not contiguous, and 3. That ends up allowing access to a lot more IP addresses than is necessary. As it turns out the first user who tested it turned out to have a different IP than all the blocks I'd added.
    – vkiwi
    Mar 30, 2016 at 6:42

1 Answer 1

-- Step 1: Create a Custom Database Role
USE YourDatabase;
CREATE ROLE FirewallRuleManager;

-- Step 2: Grant EXECUTE Permission on the sp_set_database_firewall_rule stored procedure to the custom role
GRANT EXECUTE ON sp_set_database_firewall_rule TO FirewallRuleManager;

-- Step 3: Add Users to the Custom Role
USE YourDatabase;
ALTER ROLE FirewallRuleManager ADD MEMBER YourUserName;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.