PRO: Login Blocks #

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.

Starting in 1.37, login blocks provide a simple way for the PRO Edition to configure permissions for a login. A sample looks like this:

login "PROD\read-only" {
    permissions = ["view server state"]
    user_databases {
        roles = ["db_datareader"]
    }
    database "msdb" {
        roles = ["db_datareader", "SQLAgentReaderRole"]
    }
}

This block takes takes the following actions:

1. Create the PROD\read-only login if it doesn’t exist
2. Grant view server state to the login
3. In each user database, create the user and add it to the db_datareader role
4. In the msdb database, create the user and add it to the db_datareader and SQLAgentReaderRole roles

Login Block Schema #

• The login attribute is optional and is only used for variables.
• There can be multiple database and user_databases blocks.

login "account_name" {
    
    # optional - overrides account_name above
    # only used with variables
    login = "account_name" 
    sqldsc_credential = "credential_name"

    # configuring the login
    disabled = true|false
    default_database = "database"
    sid = "sid"
    check_policy = true|false
    check_expiration = true|false
    
    roles = ["server", "roles"]
    permissions = ["server", "permissions"]

    database "db_name" {
        default_schema = "dbo"
        roles = ["database", "roles"]
        permissions = ["database", "permissions"]
        exec = ["procs", "to", "grant", "exec"]
        schema "schema_name" {
            permissions = ["schema", "permissions"]
        }
    }

    user_databases {
        default_schema = "dbo"
        include = ["limit", "list"]
        exclude = ["not", "these", "databases"]
        permissions = ["select", "exec"]
        roles = ["database", "roles"]
        schema "schema_name" {
            permissions = ["insert", "update"]
        }
    }
}

Notes #

1. SQL Server 2008 isn’t officially supported. But if you are using login blocks on SQL Server 2008, set the default_schema to "” (an empty string) and you can create domain groups with no default schema.
2. Starting in 1.43, if you have a database block for a non-existent database, it will be skipped. This is useful for system databases that may or may not exist such as SSISDB. An example is adding a login for backups. It would need to be added to each system database that exists. Those will need to be listed in the login block but will be skipped if they aren’t present.