PRO: Login Blocks

PRO: Login Blocks #

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

While in beta, you can enable the Professional Edition by creating an account and downloading a license from https://portal.sqldsc.com.

The PRO edition will become a paid version. Until then, PRO features expire 60 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" {
        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.