Adding Managed Identity to SQL DB

These are the SQL commands used to add Azure Managed Identities into a SQL DB when your azure App Service is configured to use System Assigned Managed Identity.

Note: Replace <app-name> with the name of your app service resource. /slots.deploy is the generic way to also assign your deploy slots.

CREATE USER [<app-name>] FROM EXTERNAL PROVIDER 
CREATE USER [<app-name>/slots/deploy] FROM EXTERNAL PROVIDER 

ALTER ROLE db_datareader ADD MEMBER [<app-name>] 
ALTER ROLE db_datawriter ADD MEMBER [<app-name>] 
ALTER ROLE db_datareader ADD MEMBER [<app-name>/slots/deploy]
ALTER ROLE db_datawriter ADD MEMBER [<app-name>/slots/deploy] 

In some instances you made need to give the Managed ID db owner access to creat tables on first deploy of umbraco for example.

  ALTER ROLE db_owner ADD MEMBER [<app-name>/slots/deploy] 

Note: This should should only ever need to be actioned on the deploy slot with in a WIP or QA environment and should always be revoked upon completion.