MS SQL Lineage Scanner

Scanned and supported objects

While we strive for comprehensive lineage capture, certain dataflows and transformations might be incomplete or unavailable due to technical constraints. We continuously work to expand coverage and accuracy.

Supported statements types are as follows:

CREATE TABLE AS SELECT
CREATE VIEW
INSERT
MERGE
SELECT
UPDATE

Supported database objects for Design Time Lineage are as follows:

Views
Materialized views
Lineage for stored procedures
Local and global temporary tables (created within stored procedures)

Limitations

The following SQL constructs are not supported. This means that statements containing these SQL constructs will not be included in the lineage diagram.

Table value constructor
Table functions
References to linked server objects

Lineage is also not supported for:

Jobs
Dynamic SQL
Computed columns

Supported connectivity

Connector type: JDBC.
Authentication method: SQL Server Authentication using SQL login and password.

MS SQL permissions

To ensure the scanner can access the necessary system views, the following database permissions must be granted:

Server-level permissions

VIEW ANY DATABASE: Allows the user to view metadata about all databases on the SQL Server instance. Required for querying the master.sys.databases view.

You can use the following command to set this permission:
```
GRANT VIEW ANY DATABASE TO [YourUserOrRole];
```

Database-level permissions

The user connecting to the scanner database needs to be able to query these system views for each scanned database:

INFORMATION_SCHEMA.TABLES
INFORMATION_SCHEMA.COLUMNS
INFORMATION_SCHEMA.ROUTINES
SYS.VIEWS
SYS.SCHEMAS
SYS.SQL_MODULES

User permissions

The user must have the following permissions:

CONNECT: Allows the user to connect to the database.

USE [YourDatabaseName]; GRANT CONNECT TO [YourUserOrRole];

VIEW DEFINITION: Allows the user to view the metadata of database objects. Required for accessing the schema and system views listed in previous sections.
```
USE [YourDatabaseName]; GRANT VIEW DEFINITION TO [YourUserOrRole];
```

Scanner configuration

All fields marked with an asterisk (*) are mandatory.

Property Description

Property	Description
`sourceType*`	Identifies the source type to be scanned. Must be set to `MSSQL`.
`description*`	A human-readable description of the scan.
`oneConnections`	List of Ataccama ONE connection names for future automatic pairing.
`jdbcUrl*`	Full JDBC connection string.
`username*`	MSSQL username.
`password*`	MSSQL password. Can be encrypted.
`includeDatabases`	List of databases to include in lineage extraction.
`excludeDatabases`	List of databases to exclude from lineage extraction.
`includeSchemas`	List of target schemas to include in lineage extraction. See Target filter semantics.
`excludeSchemas`	List of target schemas to exclude from lineage extraction. See Target filter semantics.
`includeTables`	List of target table names to include in lineage extraction. See Target filter semantics.
`excludeTables`	List of target table names to exclude from lineage extraction. See Target filter semantics.
`skipTableColumnsExtraction`	If set to `true`, data dictionary metadata is not extracted. Set it to `true` only for troubleshooting or testing purposes, or in specific proof-of-concept use cases. Default value: `false`.
`skipViewsExtraction`	If set to `true`, views metadata is not extracted. Default value: `false`.
`skipProceduresExtraction`	If set to `true`, procedures metadata is not extracted. Default value: `false`.

sourceType*

Identifies the source type to be scanned. Must be set to MSSQL.

description*

A human-readable description of the scan.

oneConnections

List of Ataccama ONE connection names for future automatic pairing.

jdbcUrl*

Full JDBC connection string.

username*

MSSQL username.

password*

MSSQL password. Can be encrypted.

includeDatabases

List of databases to include in lineage extraction.

excludeDatabases

List of databases to exclude from lineage extraction.

includeSchemas

List of target schemas to include in lineage extraction. See Target filter semantics.

excludeSchemas

List of target schemas to exclude from lineage extraction. See Target filter semantics.

includeTables

List of target table names to include in lineage extraction. See Target filter semantics.

excludeTables

List of target table names to exclude from lineage extraction. See Target filter semantics.

skipTableColumnsExtraction

If set to true, data dictionary metadata is not extracted. Set it to true only for troubleshooting or testing purposes, or in specific proof-of-concept use cases.

Default value: false.

skipViewsExtraction

If set to true, views metadata is not extracted.

Default value: false.

skipProceduresExtraction

If set to true, procedures metadata is not extracted.

Default value: false.

Target filter semantics

The target filters (includeSchemas, excludeSchemas, includeTables, excludeTables) follow SQL Server LIKE behavior and are case-insensitive.

The following rules apply:

Wildcards follow SQL Server semantics: % (any substring), _ (single character), [abc] and [^abc] (character classes).
Exclude filters have priority. If a target matches both an include and an exclude filter, the target is excluded.
A bare table name (for example, Table1) matches that table name across any included schema.
A fully-qualified schema.table entry (for example, SchemaB.Table2) is split on the first dot that falls outside a […] bracket span, and matches both schema and table parts.

SQL Server’s bracket-delimited identifiers can contain literal dots (for example, [my.table]), and those dots never act as split points. For example: sch.[tbl.with.dots] is split as schema sch and table [tbl.with.dots] (a literal bracket-quoted table name containing a dot), not as schema sch.[tbl and table dots].
Schema and table filters are combined with AND logic. A fully-qualified includeTables entry cannot re-include a schema that is excluded by excludeSchemas or absent from includeSchemas. For example, with includeSchemas: [dbo] and includeTables: [Table1, Sales.Table2], only dbo.Table1 is included; Sales.Table2 is excluded by the schema filter.
Empty or whitespace-only filter values are ignored.
Invalid patterns (for example, those containing unbalanced [ and ], control characters, single quotes, backslashes, or those longer than 256 characters) stop the scan with ScannerException.
Filters don’t apply to table columns.

MS SQL scanner example configuration

{
   "scannerConfigs": [
      {
         "sourceType": "MSSQL",
         "description": "Scan MSSQL using JDBC",
         "includeDatabases": ["_DemoDB"],
         "excludeDatabases": ["SANDBOX_DB", "OTHER_NON_PROD_DB"],
         "excludeSchemas": ["tmp_%", "stg_%"],
         "excludeTables": ["%_backup", "%_old"],
         "connection": {
            "jdbcUrl": "jdbc:sqlserver://localhost;encrypt=true;trustServerCertificate=true",
            "username": "ATA_LINEAGE_EXTRACTION_USER",
            "password": "@@ref:ata:[MSSQL_PASSWORD]"
         }
      }
   ]
}

Additional features

The MS SQL scanner supports the same SQL anomaly detection checks as the Snowflake scanner. See SQL DQ (anomaly) detection for more details.

Was this page useful?