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.


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)!


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.


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


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

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}';


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",

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.