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?