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 Blocks #

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. login may be used in place of 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)
    • check_policy (bool)
    • check_expiration (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.
    • ensurepresent 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 Blocks #

  • database
    • name (string - optional starting in 1.18. If not provided, it defaults to the resource name. database may be used in place of 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)
  • schema
    • schema (string - required)
    • database (string - required)
    • owner (string - optional - defaults to ‘dbo’)
  • schema_permission
    • schema (string - required)
    • database (string - required)
    • user (string - required - user or role to grant or revoke permission)
    • permission (string - required - EXEC, SELECT, etc.)
  • 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 Blocks (BETA) #

These are enabled in the beta builds. This release only supports single-step TSQL jobs.

  • agent This sets the SQL Server Agent properties (starting in 1.37.15)
    • max_history (int - optional) Maximum size of the jog history log (in rows). -1 is unlimited.
    • max_job_history (int - optional) Maximum job history rows per job. Must be less and or equal to max_history.
  • agent_operator
    • name (string - optional starting in 1.18. If not provided, it defaults to the resource name.)
    • email (string - required)
  • agent_job
    • job (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
    • step (string - required) The step name
    • command (string) These can be multiline strings
    • database (string) The database where the command will run.
    • capture_output (bool) Capture job step output to the database. Checks “Log to table” and “Include step output in history” in the job step. Sets @flags = 12. (Starting in 1.37.14)
  • agent_job_schedule
    • job (string - required) Job name
    • schedule (string - required) Schedule name. Defaults to the resource name.
    • frequency (string - required) “once”, “daily”, “weekly” (weekly starting in 1.37.15)
    • interval (string - optional) “1h”, 2m”, “10s” Daily or weekly jobs with no interval will run once per day at the start_time.
    • start_date (string - optional) “2020-01-15”
    • end_date (string - optional) “2020-01-16”
    • start_time (string - optional) “13:14:15”
    • end_time (string - required) “13:14:22”

Breaking Changes #

  • Starting in 1.37.14, the Job Step Name field is step and the Job Schedule Name field is schedule.
  • Starting in 1.35, the login_name attribute is replaced by login and the role_name attribute is replace by role. This affects server_role_member and server_permission.
  • 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



  • The EOF can be any unique string not appearing in command.
  • The ending EOF needs to be on a line by itself but can have leading white space.
  • The command text will be “dedented” to remove consistent white space at the start of each line. That makes it easier to format the command.