Azure SQL database is a managed SQL Server database service in Azure cloud. Azure SQL Database supports standard SQL Server features and provides high availability and disaster recovery capabilities to ensure that applications can run uninterrupted in case of failure. It also offers features such as built-in intelligence, advanced security, and seamless integration with other Azure services, making it a popular choice for modern application development and data management. This article will teach you how to Grant Access to Azure SQL Database.
Azure SQL database can be accessed using:
1. SQL Server authentication account.
2. Azure AD (entra id) user account.
3. Managed identity. Etc.
SQL Server user access to Azure SQL database
1. Connect to Azure SQL server as an admin via SQL Server Management Studio (SSMS) or Azure Data Studio from on-premises.
2. Select Master database to create a login for the user.
CREATE LOGIN loginname WITH PASSWORD = 'user password'
3. Open a new connection window in the selected client tool and select your Azure SQL database where you need to create the user.
CREATE USER username FROM LOGIN loginname WITH DEFAULT_SCHEMA=dbo
4. Assign roles to the user username on the database. E.g., add db_datareader and db_datawriter roles to the user username.
EXEC sp_addrolemember 'db_datareader', 'username'
EXEC sp_addrolemember 'db_datawriter', 'username'
5. To grant select access to a specific table, you can use the below script.
GRANT SELECT ON [dbo].[my_table] TO [username]
6. The script below can grant update access on a specific table to the user username.
GRANT UPDATE ON [dbo].[my_table] TO [username]
7. The below script can grant execute permission to a procedure to the user username.
GRANT EXECUTE ON [dbo].[sp_myprocedure] TO [username]
Azure active directory user access to Azure SQL database
Azure Active Directory (Entra ID) users with admin permission on Azure SQL database can only grant access to an Azure Active Directory user account. If you do not have an Active Directory Admin account set for Azure SQL database, you can do this with the steps below.
- Navigate to Azure SQL database resource in the Azure portal and Click on Microsoft Entra ID under the Settings section.
- Click on the Set admin button.
- Search for the Microsoft Entra ID user or security group to which you want to grant admin access.
- Click Select, and this will select the admin account.
- Click Save to enable the admin.
1. Connect to Azure SQL server as an admin via SQL Server Management Studio or Azure Data Studio from on-premises.
2. Select Master database to create Azure AD Account on the Azure SQL server. Run the below script to add abc@azureops.org user account. It is not mandatory to create a user on the master database. However, the master database is the default database when we use client tools like SQL Server Management Studio, and without a user on the master database, the connection may result in an error.
CREATE USER [abc@azureops.org] FROM EXTERNAL PROVIDER
3. Connect to the Azure SQL database where you want to grant access to abc@azureops.org. and create a user
CREATE USER [abc@azureops.org] FROM EXTERNAL PROVIDER
4. Assign roles to the user username on the database. E.g., add db_datareader and db_datawriter roles to the user abc@azureops.org.
EXEC sp_addrolemember 'db_datareader', 'abc@azureops.org'
EXEC sp_addrolemember 'db_datawriter', 'abc@azureops.org'
5. To grant select access to a specific table, you can use the below script.
GRANT SELECT ON [dbo].[my_table] TO [abc@azureops.org]
6. The script below can grant update access on a specific table to the user username.
GRANT UPDATE ON [dbo].[my_table] TO [abc@azureops.org]
7. The below script can grant execute permission to a procedure to the user username.
GRANT EXECUTE ON [dbo].[sp_myprocedure] TO [abc@azureops.org]
Managed identity access to Azure SQL database
Managed identity is a service principal associated with resources in Azure. An example of such a resource is Azure Data Factory. Now that we have an Microsoft Entra ID admin account set for the Azure SQL database, you can grant Azure Data Factory Managed Identity access to the Azure SQL database. You can use Azure SQL database client tools like SQL Server Management Studio 18 or Directly from the Azure SQL database portal. If you want to grant permission using client tools like SSMS 18, log in to Azure SQL database using Active Active Directory – Universal with MFA and use an Azure SQL admin account.
1. Create a user in the Azure SQL database for Azure Data Factory.
--Add Azure Data Factory managed identity as an account to Azure SQL database.
CREATE USER [name_of_the_adf] FROM EXTERNAL PROVIDER;
2. Grant roles to this newly created user based on your needs.
--Grant appropriate role (E.g. db_owner) to Azure Data Factory based on your needs.
ALTER ROLE [db_owner] ADD MEMBER [name_of_the_adf];
You can also grant granular access to managed identity, as described above.
We have seen various ways to grant access to Azure SQL database.
See more
Pro tips:
1. Refer to this article to learn how to establish a connection to Azure SQL database from Azure data factory using the managed identity.
2. Connection to Azure SQL database from SSMS might give an error “The server principal “username” is not able to access the database “master” under the current security context”. Read this article to fix this issue.
3. Microsoft renamed Azure Active Directory as Azure Entra ID.
4. Schedule T-SQL workloads on Azure SQL databases in just a few clicks using Azure Elastic Jobs Manager. Learn more about it.
Kunal Rathi
With over a decade of experience in data engineering and analytics, I've assisted countless clients in gaining valuable insights from their data. As a dedicated supporter of Data, Cloud and DevOps, I'm excited to connect with individuals who share my passion for this field. If my work resonates with you, we can talk and collaborate.
I am always interested in new challenges so if you need consulting help, reach me at kunalrathi55@gmail.com.