> For the complete documentation index, see [llms.txt](https://v2.dataos.info/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://v2.dataos.info/references/v1/resources/nilus/roles-and-permissions/ms-sql-server-minimum-permissions.md).

# MS SQL Server

This page defines the minimum Microsoft SQL Server (MSSQL) grants a platform or database administrator must provision for a Nilus pipeline user. It covers every pipeline type Nilus supports against an MSSQL depot: batch reads and writes, change data capture (CDC) through SQL Server native CDC and the Debezium SQL Server connector, and Hera metadata ingestion. Grant only the privileges that match the pipeline types a given user actually runs.

## Read data: MSSQL as source (`type: batch`)

Used when the depot is referenced in `source.address` of a Nilus pipeline config. Nilus issues `SELECT` queries (full scan or incremental via a cursor column) against the target table.

### Minimum grants

```sql
-- Database access
GRANT CONNECT ON DATABASE::<database_name> TO <user>;

-- Schema-level read
GRANT SELECT ON SCHEMA::<schema_name> TO <user>;
```

To restrict access to a specific table only, grant `SELECT` on the table instead of the schema:

```sql
GRANT SELECT ON <schema_name>.<table_name> TO <user>;
```

## CDC source: MSSQL (`type: cdc`)

Nilus captures row-level changes via SQL Server's native CDC mechanism using the Debezium SQL Server connector. The connector reads from SQL Server change tables, which are populated by SQL Server Agent jobs.

### Server-side prerequisites (one-time, requires `sysadmin` or `db_owner`)

**Step 1: Enable CDC on the database.**

```sql
USE <database_name>;
EXEC sys.sp_cdc_enable_db;

-- Verify
SELECT name, is_cdc_enabled FROM sys.databases WHERE name = N'<database_name>';
```

**Step 2: Enable CDC on each table to be captured.**

```sql
EXEC sys.sp_cdc_enable_table
    @source_schema        = N'<schema_name>',
    @source_name          = N'<table_name>',
    @role_name            = NULL,
    @supports_net_changes = 1;

-- Verify
SELECT * FROM cdc.change_tables;
```

By default, `@role_name` is `NULL` and any user with `db_datareader` can read the change tables. Setting `@role_name` to a role name restricts read access to members of that role, so the CDC user must be added to it.

{% hint style="warning" %}
If `@role_name` is set to a non-null value, the CDC connector cannot read the change table until the CDC user is added to that role, even though CDC itself is enabled.
{% endhint %}

**Step 3: Confirm SQL Server Agent is running.**

Two SQL Agent jobs are auto-created per database: `cdc.<db>_capture` (reads the transaction log) and `cdc.<db>_cleanup` (removes expired change data). CDC does not function while the Agent is stopped.

### Runtime user grants

```sql
-- Read access on all user tables in the captured database
EXEC sp_addrolemember N'db_datareader', N'<cdc_user>';

-- Read access on CDC change tables (stored in the cdc schema)
GRANT SELECT ON SCHEMA::cdc TO <cdc_user>;

-- Required for SQL Server Agent status check at connector startup
GRANT VIEW SERVER STATE TO <cdc_user>;
```

`db_datareader` grants read access to user tables, but it does not extend to the `cdc` schema where change tables are stored. Grant `SELECT` on the `cdc` schema separately so the connector can read captured rows. As an alternative to `db_datareader`, grant column-scoped `SELECT` on each captured table and add the user to the CDC gating role if `@role_name` was configured.

{% hint style="warning" %}
`db_datareader` alone does not cover the `cdc` schema. Add `GRANT SELECT ON SCHEMA::cdc` explicitly, or the connector fails to read change data even though role membership succeeds.
{% endhint %}

## Write data: MSSQL as destination (`type: batch`, `type: cdc`)

Used when the depot is referenced in `sink.address` of a Nilus pipeline config. Nilus manages table creation and data loading.

### Minimum grants (covers all strategies)

```sql
-- Database access
GRANT CONNECT ON DATABASE::<database_name> TO <user>;

-- Table creation (required if Nilus auto-creates the destination table)
GRANT CREATE TABLE TO <user>;

-- Schema access
GRANT ALTER ON SCHEMA::<schema_name> TO <user>;

-- DML on existing tables
GRANT INSERT, UPDATE, DELETE, SELECT ON SCHEMA::<schema_name> TO <user>;
```

### Grants by incremental strategy

| Strategy  | Required privileges                                                              |
| --------- | -------------------------------------------------------------------------------- |
| `append`  | `CONNECT`, `INSERT`, `CREATE TABLE` (if new table)                               |
| `merge`   | `CONNECT`, `INSERT`, `UPDATE`, `DELETE`, `SELECT`, `CREATE TABLE` (if new table) |
| `replace` | `CONNECT`, `INSERT`, `DELETE` / `TRUNCATE TABLE`, `CREATE TABLE` (if new table)  |

## Hera metadata ingestion: MSSQL source connector (`type: metadata`)

Used when the depot is referenced in `source.address` of a `type: metadata` Nilus pipeline. All metadata workflow steps run for MSSQL: the Nilus orchestration layer excludes none of them for this source type.

### Workflow steps that run for MSSQL

| Workflow step                                        | Runs? | Mode             |
| ---------------------------------------------------- | ----- | ---------------- |
| Metadata (tables, views, columns, stored procedures) | Yes   | Always           |
| Lineage                                              | Yes   | Always           |
| Usage                                                | Yes   | Always           |
| Profiler                                             | Yes   | `deep` mode only |
| Classification                                       | Yes   | `deep` mode only |

### Grants by workflow type

**Basic metadata (tables, views, columns, stored procedures, profiler):**

```sql
-- Minimum: SELECT on all objects to be ingested
GRANT SELECT TO <user>;
```

**View definitions and view lineage:**

```sql
-- Database-level (grant in every database being ingested)
GRANT VIEW DEFINITION TO <user>;

-- OR server-level (grant to the login, covers all databases)
GRANT VIEW ANY DEFINITION TO [<login_name>];
```

Without `VIEW DEFINITION`, SQL Server returns `NULL` for the view body. Views are ingested without their SQL definition, and no view lineage is created. SQL Server does not raise an error, which makes this a hard-to-diagnose gap.

{% hint style="warning" %}
Grant `VIEW DEFINITION` (or `VIEW ANY DEFINITION` at the server level) before ingesting views. Without it, view lineage fails silently instead of raising an error.
{% endhint %}

**Usage and lineage** (reads from SQL Server plan cache):

```sql
-- Required to access sys.dm_exec_cached_plans, sys.dm_exec_query_stats,
-- and sys.dm_exec_sql_text
GRANT VIEW SERVER STATE TO <user>;
```

### Additional grants summary

| Workflow                           | Grant                                    | Level                |
| ---------------------------------- | ---------------------------------------- | -------------------- |
| Basic metadata                     | `GRANT SELECT TO <user>`                 | Database             |
| View definitions / view lineage    | `GRANT VIEW DEFINITION TO <user>`        | Database             |
| View definitions / view lineage    | `GRANT VIEW ANY DEFINITION TO [<login>]` | Server (alternative) |
| Usage & lineage (query plan cache) | `GRANT VIEW SERVER STATE TO <user>`      | Server               |

## Permission matrix

| Use case              | `CONNECT` | `SELECT`       | `INSERT/UPDATE/DELETE` | `CREATE TABLE` | `db_datareader` | `VIEW DEFINITION` | `VIEW SERVER STATE` |
| --------------------- | --------- | -------------- | ---------------------- | -------------- | --------------- | ----------------- | ------------------- |
| Read (source, batch)  | Yes       | Yes            | No                     | No             | No              | No                | No                  |
| CDC source            | Yes       | Yes (snapshot) | No                     | No             | Yes             | No                | Yes                 |
| Write: `append`       | Yes       | No             | INSERT                 | Yes (if new)   | No              | No                | No                  |
| Write: `merge`        | Yes       | Yes            | Yes                    | Yes (if new)   | No              | No                | No                  |
| Write: `replace`      | Yes       | No             | INSERT + DELETE        | Yes (if new)   | No              | No                | No                  |
| Hera metadata (basic) | Yes       | Yes            | No                     | No             | No              | No                | No                  |
| Hera view lineage     | Yes       | Yes            | No                     | No             | No              | Yes               | No                  |
| Hera usage / lineage  | Yes       | Yes            | No                     | No             | No              | No                | Yes                 |

## Notes

* `sp_cdc_enable_db` requires `sysadmin` or `db_owner` privileges. This is a one-time admin action per database: once enabled, it cannot be disabled without dropping all CDC configuration for that database.
* `GRANT SELECT ON SCHEMA::cdc` is required separately. `db_datareader` does not include the `cdc` schema, and the CDC change tables live there, so grant it explicitly.
* Schema evolution requires a new capture instance. SQL Server CDC does not update the change table automatically when a column is added or removed. Run `sp_cdc_enable_table` again with a new `@capture_instance` name; the connector detects and migrates to the new instance automatically. Drop the old capture instance only after the migration is confirmed.

  ```sql
  -- After ALTER TABLE, create a new capture instance
  EXEC sys.sp_cdc_enable_table
      @source_schema    = N'dbo',
      @source_name      = N'<table_name>',
      @role_name        = NULL,
      @capture_instance = N'dbo_<table_name>_v2';

  -- Drop the old instance only after the connector migrates
  EXEC sys.sp_cdc_disable_table
      @source_schema    = N'dbo',
      @source_name      = N'<table_name>',
      @capture_instance = N'dbo_<table_name>_v1';
  ```


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://v2.dataos.info/references/v1/resources/nilus/roles-and-permissions/ms-sql-server-minimum-permissions.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.