PRO: Login Blocks

PRO: Login Blocks #

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.

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" {
        roles = ["database", "roles"]
        permissions = ["database", "permissions"]
        execs = ["procs", "to", "grant", "exec"]
        schema "schema_name" {
            permissions = ["schema", "permissions"]
        }
    }

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