Can non-administrative users be provisioned in Azure SQL via an ARM template?

Question

Most of the sample ARM templates I find for deploying an Azure App Service + Azure SQL database end up configuring the connection string for the web app using the administrator login credentials provided when creating the Azure SQL Server (this one from Microsoft, for example). I've also seen the App Service configured for Managed Identity and then that user granted administrative access over the Azure SQL instance via AAD.

I'd never want to provision a production application this way, but I can't find examples in a more secure configuration.

The only other info I find about provisioning non-admin users is via SQL after the Azure SQL instance is already up. This means I have to add another step outside of my ARM deployment to get my system fully functional.

As of the time this question was written, running custom Powershell scripts as part of an ARM deploy is in preview and that could be a path forward, but it's not ideal.

Can non-administrative users be provisioned in Azure SQL via an ARM template, without resorting to PowerShell?

Answer 1

If you want to create non-administrative users in Azure SQL database via arm template, it is impossible. Because Azure ARM template team just defines Administrators type and does not define user type or user property in server. We just can create SQL Admin or Azure AD Admin via ARM template. For more details, please refer to here and here

So if you want to create non-administrative users, you need to write custom scripting.

Answer 2

If you want to create the users as part of a template deployment, you can use the deploymentScripts resource to run TSQL or any arbitrary script:

https://docs.microsoft.com/en-us/azure/azure-resource-manager/templates/deployment-script-template?tabs=CLI

If you want to do it as part of the declaration of the Microsoft.SQL/servers resource, no you can't (as Jim mentioned).