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 User-Defined Types​

SQL Server supports user-defined alias types that give domain-specific names to base types. Atlas manages these with the type_alias block. Once defined, alias types can be referenced in table columns and function return types:

schema.ms.hcl
type_alias "price_type" {
  schema = schema.dbo
  type   = decimal(10,2)
  null   = false
}

Use the alias in columns and function return types by referencing type_alias.price_type:

schema.ms.hcl
table "products" {
  schema = schema.dbo
  // ...
  column "price" {
    type = type_alias.price_type
    null = false
  }
  // ...
}

function "get_order_total" {
  schema = schema.dbo
  lang   = SQL
  // ...
  return = type_alias.price_type
  // ...
}

Atlas generates CREATE TYPE [dbo].[price_type] FROM decimal(10,2) NOT NULL and references [dbo].[price_type] in table columns and function return types. When a column type changes from a base type to an alias type, Atlas generates the required ALTER TABLE ... ALTER COLUMN statements.

Defining Functions and Procedures​

Beyond tables, you can manage scalar functions and stored procedures alongside their permissions. Add a function that looks up an order total and a procedure that archives old orders:

schema.ms.hcl
function "get_order_total" {
  schema = schema.dbo
  lang   = SQL
  arg "@order_id" {
    type = int
  }
  return = type_alias.price_type
  as     = <<-SQL
  BEGIN
    DECLARE @result decimal(10,2);
    SELECT @result = [total] FROM [dbo].[orders] WHERE [id] = @order_id;
    RETURN @result;
  END
  SQL
}

procedure "archive_old_orders" {
  schema = schema.dbo
  lang   = SQL
  arg "@cutoff_days" {
    type = int
  }
  as = <<-SQL
  BEGIN
    DELETE FROM [dbo].[orders] WHERE [id] IN (
      SELECT TOP(@cutoff_days) [id] FROM [dbo].[orders] ORDER BY [id] ASC
    );
  END
  SQL
}
  • Functions use return to declare the return type (here referencing the price_type alias defined earlier). The lang attribute is required (typically SQL).
  • Procedures follow the same structure but without a return type.
  • Both support arg blocks for parameters, prefixed with @ as T-SQL requires.

Defining Permissions​

With roles in place, grant privileges on tables, types, functions, and procedures. 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 = type_alias.price_type
    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 = type_alias.price_type
    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 for tables, types, functions, and procedures:

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]
}

// Type-level: allow app_writer to reference price_type
permission {
  to         = role.app_writer
  for        = type_alias.price_type
  privileges = [REFERENCES]
}

// Function-level: app_readonly can call get_order_total
permission {
  to         = role.app_readonly
  for        = function.get_order_total
  privileges = [EXECUTE]
}

// Procedure-level: only app_admin can call archive_old_orders
permission {
  to         = role.app_admin
  for        = procedure.archive_old_orders
  privileges = [EXECUTE]
}

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

SQL Server uses GRANT EXECUTE ON OBJECT :: [schema].[name] for both functions and procedures, and GRANT REFERENCES ON TYPE :: [schema].[name] for user-defined types (GRANT Type Permissions). The REFERENCES permission on a type allows the grantee to use the type in their own table or function definitions. Unlike PostgreSQL, SQL Server does not grant EXECUTE to PUBLIC by default, so you only need to grant access to the roles that should have it.

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 type, role, grant, table, function, and procedure it will create:

Planning migration statements (19 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 alias type "price_type":
    -> CREATE TYPE [dbo].[price_type] FROM decimal(10,2) NOT NULL
  -- grant on type "price_type" to "app_writer":
    -> GRANT REFERENCES ON TYPE :: [dbo].[price_type] TO [app_writer]
  -- create "products" table:
    -> CREATE TABLE [dbo].[products] ( ... [price] [dbo].[price_type] NOT NULL ... )
  -- 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] ( ... [total] [dbo].[price_type] NOT NULL ... )
  -- 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]
  -- create "get_order_total" function:
    -> CREATE FUNCTION [dbo].[get_order_total] (@order_id int) RETURNS [dbo].[price_type] AS ...
  -- grant on function "get_order_total" to "app_readonly":
    -> GRANT EXECUTE ON OBJECT :: [dbo].[get_order_total] TO [app_readonly]
  -- create "archive_old_orders" procedure:
    -> CREATE PROCEDURE [dbo].[archive_old_orders] (@cutoff_days int) AS ...
  -- grant on procedure "archive_old_orders" to "app_admin":
    -> GRANT EXECUTE ON OBJECT :: [dbo].[archive_old_orders] TO [app_admin]

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

? 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 type, role, permission, function, and procedure, providing 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.