How to give access to an Azure SQL Database Hyperscale named replica without giving access to the main replica or any other replicas. This setup keeps the named replica separate in terms of resources and security, as it runs on its own processing system. This is helpful when you need a separate, read-only connection to the database. In this case, the named replica doesn’t share CPU or memory with the main database, and any work done on the named replica doesn’t affect the main database or other replicas. Also, users who have access to the named replica won’t have access to the main database or any other replicas.

Note : We can restrict the access when you create the Named replica on different server within the same region as source server. But not able to restrict access if you create named replica on same server as primary database hosted it is not supported scenario.

Steps to Follow

Create login on the primary SQL Server

Run the following command in the master database on the logical server that hosts the primary database to create a new login.

CREATE LOGIN [third-party-login] WITH PASSWORD = 'strong_password_here';

Get the SID (hexadecimal value) of the newly created login from the sys.sql_logins system view:

SELECT SID FROM sys.sql_logins WHERE name = 'third-party-login';

Disable the login to prevent it from accessing any databases on the server that hosts the primary replica.

ALTER LOGIN [third-party-login] DISABLE;

Set up a user account in the main read-write database.

After creating the login, connect to the primary read-write replica of your database (e.g., WideWorldImporters). You can find a sample script to restore the database here: “Restore Database in Azure SQL.” Next, create a database user associated with that login.

CREATE USER [third-party-user] FROM LOGIN [third-party-login];

As an optional step, after creating the database user, you may choose to remove the server login created earlier if you’re worried about it being re-enabled accidentally. To do this, connect to the master database on the logical server that hosts the primary database and run the following example scripts:

DROP LOGIN [third-party-login];

Set up a named replica on a different logical server

Create a new Azure SQL logical server that to be used to isolate access to the named replica. To create a named replica, this server must be in the same Azure region as the server hosting the primary replica.

Set up a login on the server hosting the named replica

Access the master database on the logical server where the named replica is hosted, as set up in the previous step. Replace “strong_password_here” with your chosen strong password. Then, add the login using the SID obtained from the primary replica:

CREATE LOGIN [third-party-login] WITH PASSWORD = 'strong_password_here', sid = 0x0...1234;

At this point, users and applications using third-party-login can connect to the named replica, but not to the primary replica.

Grant object-level permissions within the database

After configuring login authentication as explained, you can manage authorization or set object-level permissions in the database using standard GRANT, DENY, and REVOKE statements. In these statements, refer to the user name you created in the database or a database role that includes this user. Make sure to run these commands on the primary replica, as the changes will be propagated to all secondary replicas. However, they will only take effect on the named replica where the server-level login was established.

Example:

GRANT SELECT ON [Application].[Cities] to [third-party-user];