MSSQL Lineage Scanner
Scanned and supported objects
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:
-
Dynamic SQL
-
Computed columns
Supported connectivity
-
Connector type: JDBC.
-
Authentication method: SQL Server Authentication using SQL login and password.
MSSQL 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 themaster.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 |
---|---|
|
Unique name of the scanner job. |
|
Specifies the source type to be scanned.
Must contain |
|
A human-readable description of the scan. |
|
List of Ataccama ONE connection names for future automatic pairing. |
|
Full JDBC connection string. |
|
MSSQL username. |
|
MSSQL password. Can be encrypted. |
|
List of databases to include in lineage extraction. |
|
List of databases to exclude from lineage extraction. |
|
If set to Default value: |
|
If set to Default value: |
|
If set to Default value: |
{
"scannerConfigs": [
{
"name": "mssql-lineage-poc",
"sourceType": "MSSQL",
"description": "Scan MSSQL using JDBC",
"includeDatabases": ["_DemoDB"],
"excludeDatabases": ["SANDBOX_DB", "OTHER_NON_PROD_DB"],
"connection": {
"jdbcUrl": "jdbc:sqlserver://localhost;encrypt=true;trustServerCertificate=true",
"username": "ATA_LINEAGE_EXTRACTION_USER",
"password": "@@ref:ata:[MSSQL_PASSWORD]"
}
}
]
}
Additional features
The MSSQL scanner supports the same SQL anomaly detection checks as the Snowflake scanner. See SQL DQ (anomaly) detection for more details.
Was this page useful?