How to create database user and assign role to it with terraform

Question

I have created sql server and database with terraform now I have to create database user and assign role to it.

How I can do it. Please suggest me.

Answer 1

I've started working on a Terraform provider that will do this over at https://github.com/jayway/terraform-provider-mssql. It's still very early work, but I've managed to make it provision logins and users for those logins in a local SQL Server instance (that was already running, so I haven't tested it at all in conjunction with e.g. the AzureRM provider). Testing and contributions are very welcome (but don't use it for production scenarios just yet)!

Answer 2

Creating users and roles is a function of SQL server, not of the Azure platform. As such Terraforms ARM provider does not deal with this, and as far as I can see Terraform does not have an MSSQL provider to this work (it does have MYSQL and Postgres). You would need to do this work using SQL scripts.

Answer 3

Here's how I did this using postgres/psql.

First, generate the md5 password hash by appending the username to the password:

$ echo -n mypasswordbob|md5sum
fd9c9714184c4ae189ca83f1c21aeeb8

Prepend the string 'md5' to the resulting hash to get the encrypted password form that Postgres will accept: md5fd9c9714184c4ae189ca83f1c21aeeb8

Then add in a standalone template file with the database commands you want to run:

data "template_file" "db_roles" {
  vars {
    username_bob = "bob"
    password_bob = "md5fd9c9714184c4ae189ca83f1c21aeeb8"
  }
  template = <<EOF

  create user $${username_bob};
  alter user $${username_bob} WITH ENCRYPTED PASSWORD '$${password_bob}';

  EOF
}

Finally, add a remote-exec provisioner as part of your server creation:

  provisioner "remote-exec" {
    inline = [
      "set -x",
      "cat > db_roles.sql <<EOL\n${data.template_file.db_roles.rendered}\nEOL",
      "psql -U myuser < db_roles.sql",
    ]
  }

Answer 4

Use the Terraform file provisioner to deploy the SQL, then use remote-exec provisioner to load it into SQL Server.