How to Grant Access to Azure SQL Database

To grant access to an Azure SQL Database, create a database user and then assign the required roles or permissions. Azure SQL Database supports SQL authentication, Microsoft Entra authentication, and managed identities.

Quick answer: Connect to the target database with an account that has sufficient permissions, create the user, and grant only the access it needs.

SQL authentication access

For access to a single database, a contained database user is usually the simplest option.

<pre>CREATE USER [username] WITH PASSWORD = 'StrongPasswordHere';</pre>

For read-only access:

<pre>ALTER ROLE [db_datareader] ADD MEMBER [username];</pre>

For read/write access:

<pre>ALTER ROLE [db_datareader] ADD MEMBER [username];
ALTER ROLE [db_datawriter] ADD MEMBER [username];</pre>

For least-privilege access, grant permissions only on the required objects:

<pre>GRANT SELECT ON [dbo].[my_table] TO [username];
GRANT UPDATE ON [dbo].[my_table] TO [username];
GRANT EXECUTE ON [dbo].[sp_myprocedure] TO [username];</pre>

If the same SQL login needs access to multiple databases, create a SQL authentication login in the virtual master database and map it to a user in each target database.

<pre>-- Run in master
CREATE LOGIN [loginname] WITH PASSWORD = 'StrongPasswordHere';

-- Run in each target database
CREATE USER [username] FROM LOGIN [loginname];</pre>

Microsoft Entra access

First configure a Microsoft Entra administrator for the logical SQL server. In the Azure portal, open the SQL server and select Microsoft Entra ID to configure the administrator.

Connect to the target database using a Microsoft Entra identity with sufficient permissions and create a contained database user:

<pre>CREATE USER [user@domain.com] FROM EXTERNAL PROVIDER;
ALTER ROLE [db_datareader] ADD MEMBER [user@domain.com];</pre>

Microsoft Entra groups can also be created as database principals, which is useful when access is managed through group membership.

Grant Azure Data Factory managed identity access

For Azure Data Factory, grant the factory’s managed identity access to Azure SQL without storing database credentials.

<pre>CREATE USER [name_of_the_adf] FROM EXTERNAL PROVIDER;
ALTER ROLE [db_datareader] ADD MEMBER [name_of_the_adf];</pre>

Replace db_datareader with the minimum role or permissions required. Avoid db_owner unless full database control is genuinely required.

Grant access using the Azure portal Query editor

Azure SQL Database provides a Query editor in the Azure portal for running T-SQL without a separate client tool. Open the database, select Query editor (preview), authenticate, and run the appropriate CREATE USER, ALTER ROLE, or GRANT statements.

Common access problems

• Firewall error: Check the logical server networking settings and allow the client’s IP or private network path. Azure SQL Database uses TCP port 1433.
• Authentication succeeds but database access fails: Verify that the user exists in the target database and has the required role or permission.
• Microsoft Entra authentication fails: Verify that a Microsoft Entra administrator is configured and the identity has been created as a database user.
• SSMS reports a master database error: Check the principal’s access to the database used by the connection. See our master database access error guide.

See more

Related AzureOps guides:
• Connect Azure SQL from Data Factory using managed identity.
• Cross-database queries in Azure SQL Database.