PRO: User Databases Block #

All features described on this page are part of the Professional Edition.

You can purchase licenses for the Professional Edition on the Portal: https://portal.sqldsc.com.

User Databases Block #

Starting in 1.36, the PRO Edition provides a mechanism to make identical changes across all user databases. This example creates a “ro” schema and a “ReadOnlyRole” in each user database. Then it grant the “ReadOnlyRole” EXEC permission on the schema and adds “ReadOnlyRole” to the db_datareader role in each database.

user_databases {
    resource "schema" "ro" {
        schema = "ro"
        owner = "dbo"
    }
    resource "database_role" "ReadOnlyRole" {
        role = "ReadOnlyRole"
        owner = "dbo"
    }
    resource "schema_permission" "ro-exec" {
        schema = "ro"
        user = "ReadOnlyRole"
        permission = "EXEC"
    }

    resource "database_role_member" "ro-reader" {
        user = "ReadOnlyRole"
        role = "db_datareader"
    }
}

This example creates an ro schema, a ReadOnlyRole, and grants EXEC on the schema to the role in each user database.

This will apply the resource blocks enclosed in the user_databases block to each user database on the server. User databases are defined as any database except master, model, tempdb, msdb, ReportServer, ReportServerTempDB, SSISDB, and distribution.

The list of user databases can be modified using the include and exclude attributes. This example only processes the three databases in the include attribute.

user_databases {
    include = ["db1", "db2", "db3"]
}

And this example processes all databases except SecretPasswordDB

    exclude = ["SecretPasswordDB"]

There can be multiple user_databases clauses in an HCL file.

Database Block #

A database block is basically a user_databases block but for one database.

database "msdb" {
    resource "database_user" "PROD\ReadOnly" {}
    resource "database_role_member" "msdb-ro" {
        user = "PROD\ReadOnly"
        role = "db_datareader"
    }
}

This example adds a domain user to msdb and puts them in the db_datareader role.

Concerns #

When released in version 1.37, this doesn’t pick up databases added during the run. It builds the list of databases at the start. This will be fixed in a future build.

Consider a server with 50 databases and a user_databases block with 10 Resource Blocks. That will make at least 500 round trips to the database server to process. Some Resource Blocks need multiple queries. Making the actual change will be a second round trip. Since each Resource Block may or may not mutate the server state, it is very difficult to cache the server state. This can take a noticeable amount of time across slow links.