Resource Block Schema

Resource Block Schema #

Each resource block MAY have an ensure attribute. The possible values are:

  • present - If the resource doesn’t exist, create it. If it exists, alter it to match the resource block.
  • absent - If the resource exists, remove it
  • alter - If the resource exists, alter it. If it doesn’t exist, do nothing.

If no ensure attribute is found, it defaults to present

Server Resource Block Types #

The following resource block types are supported

  • configuration
    • name (string - optional. If not provided, it defaults to the resource name) Any valid name from sys.configurations where is_dynamic = 1. Common value are:
      • backup compression default
      • clr enabled
      • cost threshold for parallelism
      • max degree of parallism
      • max server memory (MB)
      • max text repl size (B)
      • optimize for ad hoc workloads
      • xp_cmdshell
    • value (int32 - required)
    • Configuration entries can’t be created or deleted. Ensure of present is treated like alter and absent isn’t a valid value.
  • login
    • name (string - optional starting in 1.18. If not provided, it defaults to the resource name.)
    • disabled (true/false)
    • sqldsc_credential (string - required for SQL Server logins) - the local saved vault entry use to set the password
    • default_database (string)
    • policy_checked (bool)
    • expiration_checked (bool)
    • sid - (string - only for SQL Server logins)
  • server_permission
    • login (string - required)
    • permission (string - required)
  • server_role_member
    • login (string - required)
    • role (string - required)
  • sql_script
    • test_query – SQL query that returns rows if the script has already run. If the query returns no rows, the SQL file specified in file will be run.
    • expected_rows (int - optional) If provided, this is how many rows the test_query is expected to return. If not provided, the script will only run if the test_query returns no rows.
    • script_file – file to look for in the scripts directory. This file is run using sqlcmd.exe. Prior to 1.17 this was named file_name.
    • ensure – present will run the file if no rows are returned by test_query. All other values are an error.
    • database – run the script in this database
    • The ‘--push file.sql’ parameter will always run a script if passed on the command line. This useful for situations where you want to update a common stored procedure across servers. Typically this is used to put something like sp_whoisactive on servers.

Database Resource Block Types #

  • database
    • name (string - optional starting in 1.18. If not provided, it defaults to the resource name.)
    • owner (string)
    • recovery_model (string - full/simple/bulk_logged)
    • rcsi (true/false) read committed snapshot enabled
  • database_user
    • database (string - required)
    • user (string - required)
    • default_schema (string) - the default schema for this user. Prior to 1.18 this was named schema.
  • database_role
    • database (string - required)
    • role (string - required)
    • owner (string - optional - defaults to ‘dbo’)
  • database_role_member
    • database (string - required)
    • role (string - required)
    • user (string - required)
  • database_permission
    • database (string - required)
    • user (string - required)
    • permission (string - required)
  • object_permission sets a permission on a database object
    • database (string - required)
    • schema (string - required)
    • object (string - required)
    • user (string - required)
    • permission (string - required)

Agent Resource Block Types #

This release only supports single-step TSQL jobs.

  • agent_operator
    • name (string - optional starting in 1.18. If not provided, it defaults to the resource name.)
    • email (string - required)
  • agent_job
    • name (string - optional. If not provided, it defaults to the resource name.)
    • enabled (bool - optional. Defaults to true.)
    • owner (string - optional. Defaults to the current user.)
    • notify_on (string - optional - never|success|failure|completed) Send a notification email based on this.
    • operator (string - optional) Name of the operator to receive the email
  • agent_job_step
    • job (string - required) Job name
    • step_id (int - required) This must be 1 for this release
    • name (string - required) The step name
    • command (string) These can be multiline strings
    • database (string) The database where the command will run.

Breaking Changes #

  • In the sql_script resource, the file is now specified in the script_file field. script is also supported but will be removed in a future release.
  • The name of the resource was changed from sql_file to sql_script in 1.18 (12 April 2020)
  • Resource Blocks now overwrite previous definitions. Prior to this they would merge. It’s just too complicated to figure out where different settings come from in files in 1.22 (22 April 2020)

Multiline strings #

These can be constructed using a heredoc format. Avoid any spaces before or after the EOF.

resource "agent_job_step" "step two" {
    job = "test"
    id = 1
    name = "step 2"
    command = <<EOF
SELECT 1
SELECT 2
EOF
}