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 '16 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 '16 at 6:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.