Generating users dynamically in Azure SQL Database

Data and Databases
1

I’m trying to set up a system that automatically creates users and roles in our Azure SQL Database based on the environment. Right now we do this manually which is slow and can lead to mistakes.

Here’s what I’ve tried:

DECLARE @NewUser VARCHAR(50) = 
    CASE @Env
        WHEN 'Dev' THEN 'DevAppUser'
        WHEN 'Test' THEN 'TestAppUser'
        ELSE ''
    END

IF @NewUser != ''
BEGIN
    EXEC ('CREATE USER [' + @NewUser + '] FOR LOGIN [' + @NewUser + '];')
    EXEC ('EXEC sp_addrolemember N''AppRole'', N''' + @NewUser + ''';')
END

This works fine on our local SQL Server but Azure SQL throws an error saying the CREATE USER statement must be alone in the batch.

The docs mention this limitation but it seems to prevent any kind of automation. Has anyone found a way around this? Maybe using a different approach in a stored procedure? Any ideas would be really helpful!

2

hey, i experinced the same issue. i wrapped ‘create user’ in dynamic sql so each stmt runs alone.
not the prettiest, but it worked. hope u find it helpful!

3

I’ve encountered this limitation in Azure SQL as well. One approach that worked for me was using Azure Active Directory (AAD) integration. This allows you to manage database users and permissions through AAD groups, which can be automated more easily.

Here’s a high-level overview of the process:

  1. Set up AAD integration for your Azure SQL Database.
  2. Create AAD groups for different environments (e.g., DevAppUsers, TestAppUsers).
  3. Use Azure PowerShell or Azure CLI to automate adding users to these groups.
  4. In your database, create contained users mapped to these AAD groups.

This method provides better security and simplifies user management across different environments. It also avoids the limitations of direct SQL commands for user creation in Azure SQL.

4

hmmm, interesting problem! have u considered using azure automation or azure functions? they might offer more flexibility for dynamic user creation. what about using a script that connects to the db and executes each command separately? curious to hear ur thoughts on these approaches!