Azure SQL database user firewall

Question

I am trying to set the firewall rule for specific user account in my Azure SQL database. Currently I have two login accounts, “admin” and “user”. I want to open up the access to allow all “user” IP addresses to be able to access the database. Right now my firewall settings are blocking anyone trying to access the “user” account without having their IP address whitelisted. I’m unclear how to assign a firewall rule to a specific database login.

Answer 1

First make sure while creating the SQL Server in Azure, select Yes for Allow Azure services and resources to access this server in Networking settings.

Open the Azure SQL Server in SQL Server Management Studio.

Switch to the "master" database from the database selection box.

Create the Server Login by executing below statements.

CREATE LOGIN testLogin1
WITH PASSWORD = '<Strong_Password_Goes_Here>';

Now select the database for which you want to create the user. In another Query window, run the below statements to create the user.

CREATE USER [testLogin1]
FROM LOGIN [testLogin1]
WITH DEFAULT_SCHEMA=dbo;

At last give the db_owner permission to the new user.

ALTER ROLE db_owner ADD MEMBER [testLogin1];

Now just go to your Azure Database Query editor, and login with new user and password. It will login.