PRO: User Databases Block

PRO: User Databases Block #

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

While in beta, you can enable the Professional Edition by creating an empty key.xt file next to the sqldsc.exe file.

The PRO edition will become a paid version. Until then, PRO features expire 90 days after the build date. Please see https://www.scalesql.com for updated builds or run sqldsc update.

User Databases Block #

Starting in 1.36, the PRO Edition provides a mechanism to make identical changes across all user databases.

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

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, and distribution.

The list of 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.