Post on creating groups and assigning roles to Azure SQL Database

[dhirschfeld]

Example null_resources:

resource "null_resource" "create_user" {
  depends_on = [<database>]
  for_each  = toset([<group names>])

  triggers = {
    always_run = timestamp()
  }

  provisioner "local-exec" {
    interpreter = ["PowerShell", "-Command"]
    command     = <<-EOT
      $ErrorActionPreference = "Stop"; `
      sqlcmd `
        -S ${server-fqdn} `
        -d ${db-name} `
        -G -b `
        -Q "DROP USER IF EXISTS [${each.value.name}]; CREATE USER [${each.value.name}] FROM EXTERNAL PROVIDER"; `
      Exit $LastExitCode;
    EOT
  }
}

resource "null_resource" "assign_role" {
  depends_on = [null_resource.create_user]

  triggers = {
    group_id   = <group-id>
    group_name = <group-name>
    always_run = timestamp()
  }

  provisioner "local-exec" {
    interpreter = ["PowerShell", "-Command"]
    command     = <<-EOT
      $ErrorActionPreference = "Stop"; `
      $group = <group-name>; `
      sqlcmd `
        -S ${server-fqdn} `
        -d ${db-name} `
        -G -b `
        -Q "EXEC sp_addrolemember 'db_datareader', '$group'"; `
      Exit $LastExitCode;
    EOT
  }
}

[dhirschfeld]

Depends on https://github.com/microsoft/go-sqlcmd

[dhirschfeld]

Query to show group names/roles

select
  DP1.name as username,
  DP2.name as role,
  DP1.type_desc as type,
  DP1.authentication_type_desc as authentication_type
from
  sys.database_principals as DP1
left outer join
  sys.database_role_members as DRM
on
  DRM.member_principal_id = DP1.principal_id
left outer join
  sys.database_principals as DP2
on
  DRM.role_principal_id = DP2.principal_id
where
  DP1.type = 'X'
order by
  username;