Azure Functions With Managed Identity Access Azure SQL Specific Schema

Question

My scenario is following

• Azure Functions ( There are multiple function apps) with Managed Identity available.
• Each Function App has access to specific schema in Azure SQL. This is Single Database with Multiple schema scenario.

As per microsoft document I have to do following to allow function app access to DB.

CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];
ALTER ROLE db_ddladmin ADD MEMBER [<identity-name>];
GO

This works fine but issue is that it can access entire database. I want to allow each function app to access only single schema within a database. Specially it creates issue when EF core migration is being executed. How can we give specific function app to completely manage specific schema of database.

Answer 1

Instead of db_datareader, db_datawriter, etc. role assignment, you can use GRANT permission statement.

Refer the below example:

GRANT SELECT ON SCHEMA::HumanResources TO role_HumanResourcesDept;

where HumanResources is a schema and role_HumanResourcesDept is the user.

Refer https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver15