Unable to create user: mssql: Incorrect syntax near the keyword 'with'

[a7i]

I am running into issues creating a mssql_user. I had no issues with creating the mssql_login.

Resource

variable "roles" {
  default = ["db_datawriter", "db_datareader"]
}

variable "databases" {
  default = ["master"]
}

resource "mssql_user" "user" {
  server {
    host = var.database_host
    port = var.database_port
    login {
      username = var.database_admin_username
      password = var.database_admin_password
    }
  }
  for_each = toset(var.databases)
  username = mssql_login.login.login_name
  roles    = coalesce(var.roles, [])
  database = each.key
}

Plan output

  # module.sqlserver_role.mssql_user.user["master"] will be created
  + resource "mssql_user" "user" {
      + authentication_type = (known after apply)
      + database            = "master"
      + default_schema      = "dbo"
      + id                  = (known after apply)
      + principal_id        = (known after apply)
      + roles               = [
          + "db_datawriter",
          + "db_datareader",
        ]
      + username            = "testuser"

      + server {
          + host = "REDACTED.us-east-1.rds.amazonaws.com"
          + port = "1433"

          + login {
              + password = (sensitive value)
              + username = "rdsuser"
            }
        }
    }

Error:

2021/04/25 22:53:36 [DEBUG] module.sqlserver_role.mssql_user.user["master"]: apply errored, but we're indicating that via the Error pointer rather than returning it: unable to create user [master].[testuser]: mssql: Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
2021/04/25 22:53:36 [ERROR] eval: *terraform.EvalApplyPost, err: unable to create user [master].[testuser]: mssql: Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
2021/04/25 22:53:36 [ERROR] eval: *terraform.EvalSequence, err: unable to create user [master].[testuser]: mssql: Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

[a7i]

Looks like I'm missing login_name.

Running into another issues but seems that it's related to SQL Server 2014

[dmitry-mightydevops]

have you solved it @a7i ?

[a7i]

Hi @dmitry-mightydevops : I ended up using another provider for 2 reasons:

1. 2014 doesn't support [String_Split](https://github.com/betr-io/terraform-provider-mssql/blob/master/sql/user.go#L124) and I did not want to raise the compatibility level
2. This provider doesn't offer a Database resource (i.e. create database)