Skip to main content

SQL Server Database Security as Code (Declarative)

Managing database security through ad-hoc GRANT and REVOKE commands leads to drift, and permissions scattered across migration files make it hard to answer "who can access what?" Atlas lets you define roles, users, and permissions as code - and automatically plans the changes needed to bring any database in line with the desired state.

This guide covers the declarative workflow. For versioned migrations, see the versioned security guide.

Atlas Pro Feature

Roles, users, and permissions are available only to Atlas Pro users. To use this feature, run:

atlas login

Prerequisites​

  1. Docker
  2. Atlas installed on your machine (installation guide)
  3. An Atlas Pro account (run atlas login to authenticate)

Configuring Atlas​

Start by spinning up a local SQL Server database using atlas tool docker:

export DATABASE_URL=$(atlas tool docker --url "docker://sqlserver/2022-latest/demo?mode=database" --name my-db)
Cleaning up

When you're done, stop the container with atlas tool docker kill --name my-db.

Roles and permissions are excluded from inspection and schema management by default. To enable them, add a schema.mode block to your project configuration:

atlas.hcl
env "local" {
  url = getenv("DATABASE_URL")
  dev = "docker://sqlserver/2022-latest/dev?mode=database"
  schema {
    src = "file://schema.ms.hcl"
    mode {
      roles       = true   // Inspect and manage roles and users
      permissions = true   // Inspect and manage GRANT / REVOKE
    }
  }
}
  • roles = true - include roles and users in inspection and planning.
  • permissions = true - include GRANT / REVOKE statements.

Defining Roles​

Let's model a SaaS order management application with three tiers of access:

RolePurpose
app_readonlyRead-only access for reporting dashboards
app_writerRead-write access for the application backend, inherits from app_readonly
app_adminFull administrative access, inherits from app_writer
schema.ms.hcl
role "app_readonly" {
}

role "app_writer" {
  member_of = [role.app_readonly]
}

role "app_admin" {
  member_of = [role.app_writer]
}
  • Inheritance - member_of creates a role hierarchy. app_writer inherits all privileges of app_readonly, and app_admin inherits everything from app_writer.
  • Users - SQL Server database users can be mapped to roles using the user block with login_name to reference a server-level Login, and member_of to assign role membership. See the HCL reference for details.

Defining Permissions​

With roles in place, grant privileges at the table and column level. First, define the tables that permissions will reference:

schema.ms.hcl
table "users" {
  schema = schema.dbo
  column "id" {
    type = int
    identity {
      seed      = 1
      increment = 1
    }
  }
  column "name" {
    type = nvarchar(255)
    null = false
  }
  column "email" {
    type = nvarchar(255)
    null = false
  }
  primary_key {
    columns = [column.id]
  }
}

table "products" {
  schema = schema.dbo
  column "id" {
    type = int
    identity {
      seed      = 1
      increment = 1
    }
  }
  column "name" {
    type = nvarchar(255)
    null = false
  }
  column "price" {
    type = decimal(10,2)
    null = false
  }
  primary_key {
    columns = [column.id]
  }
}

table "orders" {
  schema = schema.dbo
  column "id" {
    type = int
    identity {
      seed      = 1
      increment = 1
    }
  }
  column "user_id" {
    type = int
    null = false
  }
  column "product_id" {
    type = int
    null = false
  }
  column "total" {
    type = decimal(10,2)
    null = false
  }
  primary_key {
    columns = [column.id]
  }
  foreign_key "fk_order_user" {
    columns     = [column.user_id]
    ref_columns = [table.users.column.id]
  }
  foreign_key "fk_order_product" {
    columns     = [column.product_id]
    ref_columns = [table.products.column.id]
  }
}

schema "dbo" {
}

Next, add the permissions:

schema.ms.hcl
// Read-only: SELECT on all tables
permission {
  for_each   = [table.orders, table.products, table.users]
  for        = each.value
  to         = role.app_readonly
  privileges = [SELECT]
}

// Writer: SELECT, INSERT, UPDATE on orders and products
permission {
  for_each   = [table.orders, table.products]
  for        = each.value
  to         = role.app_writer
  privileges = [SELECT, INSERT, UPDATE]
}

for_each keeps permissions DRY - define the grant once, and Atlas expands it for every table at plan time.

Applying Changes​

Run atlas schema apply to diff the desired state against the live database and execute the changes:

atlas schema apply --env local

Atlas produces a plan showing every role, grant, and table it will create:

Planning migration statements (13 in total):

  -- create role "app_readonly":
    -> CREATE ROLE [app_readonly]
  -- create role "app_writer":
    -> CREATE ROLE [app_writer]
  -- add role "app_writer" as member of "app_readonly":
    -> ALTER ROLE [app_readonly] ADD MEMBER [app_writer]
  -- create role "app_admin":
    -> CREATE ROLE [app_admin]
  -- add role "app_admin" as member of "app_writer":
    -> ALTER ROLE [app_writer] ADD MEMBER [app_admin]
  -- create "products" table:
    -> CREATE TABLE [dbo].[products] ( ... )
  -- grant on table "products" to "app_readonly":
    -> GRANT SELECT ON [dbo].[products] TO [app_readonly]
  -- grant on table "products" to "app_writer":
    -> GRANT INSERT, SELECT, UPDATE ON [dbo].[products] TO [app_writer]
  -- create "users" table:
    -> CREATE TABLE [dbo].[users] ( ... )
  -- grant on table "users" to "app_readonly":
    -> GRANT SELECT ON [dbo].[users] TO [app_readonly]
  -- create "orders" table:
    -> CREATE TABLE [dbo].[orders] ( ... )
  -- grant on table "orders" to "app_readonly":
    -> GRANT SELECT ON [dbo].[orders] TO [app_readonly]
  -- grant on table "orders" to "app_writer":
    -> GRANT INSERT, SELECT, UPDATE ON [dbo].[orders] TO [app_writer]

-------------------------------------------

? Approve or abort the plan:
  ▸ Approve and apply
    Abort

After approving, verify with atlas schema inspect:

atlas schema inspect --env local

The output reflects every role and permission - a single, readable snapshot of your entire security posture. Run this on any environment to confirm it matches the desired state.

Next Steps​

Have questions? Feedback? Find our team on our Discord server or schedule a demo.