Skip to main content

SQL Server Database Security as Code (Versioned)

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. With the versioned workflow, each change is captured in a migration file, reviewed by your team, and applied through CI/CD.

This guide covers the versioned workflow. For the declarative approach, see the declarative 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)

Project Setup​

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.

Create an atlas.hcl configuration file with roles and permissions enabled:

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
    }
  }
  migration {
    dir = "file://migrations"
  }
}

Defining the Desired State​

The schema file is the target state Atlas diffs against the migration directory. Let's model a SaaS order management application with tiered 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]
}

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

// 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]
}
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. When using login_name, configure a dev block with a baseline script to pre-create the Logins on the dev database. See the HCL reference for details.

Generating the Initial Migration​

Run atlas migrate diff to generate the first migration file:

atlas migrate diff add_security --env local

Atlas creates a migration directory with the generated T-SQL and a checksum file:

migrations/
├── 20260311120000_add_security.sql
└── atlas.sum

The generated migration contains every role, grant, and table:

migrations/20260311120000_add_security.sql
-- 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] (
  [id] int IDENTITY (1, 1) NOT NULL,
  [name] nvarchar(255) NOT NULL,
  [price] decimal(10,2) NOT NULL,
  CONSTRAINT [PK_products] PRIMARY KEY CLUSTERED ([id] ASC)
);
-- 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] (
  [id] int IDENTITY (1, 1) NOT NULL,
  [name] nvarchar(255) NOT NULL,
  [email] nvarchar(255) NOT NULL,
  CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED ([id] ASC)
);
-- Grant on table "users" to "app_readonly"
GRANT SELECT ON [dbo].[users] TO [app_readonly];
-- Create "orders" table
CREATE TABLE [dbo].[orders] (
  [id] int IDENTITY (1, 1) NOT NULL,
  [user_id] int NOT NULL,
  [product_id] int NOT NULL,
  [total] decimal(10,2) NOT NULL,
  CONSTRAINT [PK_orders] PRIMARY KEY CLUSTERED ([id] ASC),
  CONSTRAINT [fk_order_product] FOREIGN KEY ([product_id]) REFERENCES [dbo].[products] ([id]) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT [fk_order_user] FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([id]) ON UPDATE NO ACTION ON DELETE NO ACTION
);
-- 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];

Applying Migrations​

Apply to the target database:

atlas migrate apply --env local
Migrating to version 20260311120000 (1 migration in total):

  -- migrating version 20260311120000
    -> CREATE ROLE [app_readonly]
    -> CREATE ROLE [app_writer]
    -> ALTER ROLE [app_readonly] ADD MEMBER [app_writer]
    -> CREATE ROLE [app_admin]
    -> ALTER ROLE [app_writer] ADD MEMBER [app_admin]
    -> CREATE TABLE [dbo].[products] ( ... )
    -> GRANT SELECT ON [dbo].[products] TO [app_readonly]
    -> GRANT INSERT, SELECT, UPDATE ON [dbo].[products] TO [app_writer]
    -> CREATE TABLE [dbo].[users] ( ... )
    -> GRANT SELECT ON [dbo].[users] TO [app_readonly]
    -> CREATE TABLE [dbo].[orders] ( ... )
    -> GRANT SELECT ON [dbo].[orders] TO [app_readonly]
    -> GRANT INSERT, SELECT, UPDATE ON [dbo].[orders] TO [app_writer]
  -- ok

  -------------------------
  -- 1 migration
  -- 13 sql statements

With Atlas Cloud, push your migration directory and deploy from any CI/CD platform:

atlas migrate push app --env local

Making Incremental Changes​

When requirements change, update the schema file and generate a new migration. Atlas computes only the diff. For example, adding an app_support role for the support team:

Update the schema file:

schema.ms.hcl
role "app_support" {
  member_of = [role.app_readonly]
}

// Support can view and update orders
permission {
  to         = role.app_support
  for        = table.orders
  privileges = [SELECT, UPDATE]
}

Generate the incremental migration:

atlas migrate diff add_support_role --env local

Atlas generates only what changed - the new role and its grants:

migrations/20260311130000_add_support_role.sql
-- Create role "app_support"
CREATE ROLE [app_support];
-- Add role "app_support" as member of "app_readonly"
ALTER ROLE [app_readonly] ADD MEMBER [app_support];
-- Grant on table "orders" to "app_support"
GRANT SELECT, UPDATE ON [dbo].[orders] TO [app_support];

The migration directory now holds both files. The sum of all migrations represents the current security state:

migrations/
├── 20260311120000_add_security.sql
├── 20260311130000_add_support_role.sql
└── atlas.sum

Next Steps​

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