Create Users in Bulk for Azure Databases

Question

I will caveat this question by saying I am by no means a DBA. If there's a better way to think about this solution, I'm all ears.

Because of a recent recommendation from Microsoft, our group is transitioning off VMs running SQL Server to Azure SQL Servers, Azure Elastic Pools, and Azure Databases. Part of what was overlooked however, is the user management piece. I've read the Microsoft docs on Database access and understand how to create users. However, in the examples, it's a couple users to a couple databases. The problem comes with us having to create 20-30 users amongst a couple thousand databases.

To this end, I've been reading about Azure Elastic Jobs. This seems like it would be an adequate solution; however, even for the Elastic Job, you have to create a jobuser in each output database. While this narrows down the issue from 20-30 users to 1 user, it doesn't fully alleviate the issue.

It also doesn't solve the issue of once a new database gets spun up, we'd have to add the jobuser to that database to then add the remaining users. Again easier, but not simple like creating a user for on-premise SQL Servers.

So I guess my question is, is there no way to manage users in bulk through Azure or SQL for Azure SQL Servers? Is the only answer to use PowerShell or a 3rd party solution?

The current solution is to run create logins from PowerShell/Python.

Answer 1

I would say the easiest solution to your problem is using Azure security groups.

1. Create an Azure security group

2. Create a login for that security group as an external provider shown below

   CREATE LOGIN [security-group-name] FROM EXTERNAL PROVIDER; -- on master db level

3. Create user at database level for that security group

   CREATE USER [security-group-name] FROM LOGIN [security-group-name]; -- at a database level

4. Grant permissions to the [security-group-name]

5. Add the users to the security group.

you may still need to create a user each for all the databases you have. but you don't have to do it for every individual user, as they inherit the privileges from the group.