SQL Server

Overview

Solid connects to SQL Server to extract schema metadata and query history for the Solid analysis process. Solid requires access to:

• The schema of all relevant SQL Server databases
• Query logs and execution history
• Read access to the data itself (via db_datareader, or schema-scoped SELECT) — this is required, not optional, for Solid's Text2SQL engine to generate and validate SQL

What Is Collected

📘

Per-user attribution is not available on SQL Server

Query Store does not record which user executed each query, so Solid cannot report per-user usage from SQL Server query history. Query text, frequency and timing are collected normally. If per-user attribution is a requirement, it can be captured separately with Extended Events or SQL Audit — contact us to discuss the setup.

Automatic Pull (Recommended)

To run the metadata and query log extraction scripts, you'll need a SQL Server login with specific permissions. It is a security best practice to create a dedicated, low-privilege user for this purpose. Permissions for this user are listed in Permissions Needed below.

Create the Login and Grant Permissions

Use the following T-SQL script to create a new login and grant it the necessary permissions.

-- 1. Create a new SQL Server Login (server-wide — run this once only)
-- This command should be run in the 'master' database
USE master;
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'solid_user')
    CREATE LOGIN solid_user WITH PASSWORD = '<YourStrongPassword>';

Step 1 is server-wide — run it once, not per database. Re-running CREATE LOGIN for a login that already exists fails with Msg 15025, which is why it's guarded above.

VIEW SERVER STATE is not required for the core setup. Solid reads metadata from INFORMATION_SCHEMA views and sys.objects, and query history from Query Store — none of which requires VIEW SERVER STATE. That permission is a server-wide DMV privilege needed only if you choose to use Method 2 (DMVs) for query history extraction. If you enable Method 2, add it at the server level: GRANT VIEW SERVER STATE TO solid_user;

-- 3. Create a User and Grant Database-Level Permissions
-- Run steps 3 and 4 on EACH database you wish to monitor
USE [<YourDatabaseName>]; -- Replace with your database name

-- Create a database user mapped to the server login
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'solid_user')
    CREATE USER solid_user FOR LOGIN solid_user;

-- Grant permissions to view database metadata and query store
GRANT VIEW DATABASE STATE TO solid_user;
GRANT VIEW DEFINITION TO solid_user;

-- 4. Grant Read-Only Data Access (REQUIRED)
-- Recommended: add the user to db_datareader so SELECT is granted on all
-- current and future user tables and views in this database.
ALTER ROLE db_datareader ADD MEMBER solid_user;

-- Alternative (schema-scoped), if you must limit access to specific schemas:
-- GRANT SELECT ON SCHEMA::[dbo] TO solid_user;
-- Repeat for each schema Solid should read.

Important:

• Replace <YourStrongPassword> with a strong password
• Replace <YourDatabaseName> with each database you want to monitor
• Run steps 1 and 2 once per server; repeat only steps 3 and 4 for each additional database

🚧

Do not add GRANT VIEW DATABASE PERFORMANCE STATE

That permission only exists on SQL Server 2022+ and Azure SQL Managed Instance. On SQL Server 2019 and earlier it fails with Incorrect syntax near 'VIEW' — and because that is a parse error, the entire batch is aborted and the login is left without its database permissions. VIEW DATABASE STATE covers everything Solid needs.

Metadata Extraction

Gather information from system tables to understand the structure of your databases, including details about tables, columns, data types, schemas, keys, relationships, view definitions and documentation.

📘

Column names matter

Solid matches these result sets by column name. The aliases in the scripts below are part of the interface — please keep them exactly as written. In particular the database column must be named TABLE_CATALOG: Solid uses it to keep identically-named tables in different databases apart, and the extraction will fail without it.

Option 1: Using INFORMATION_SCHEMA Views (Recommended)

The INFORMATION_SCHEMA views provide a standardized, SQL-92 compliant way to retrieve metadata. This script iterates through all user databases and collects the full column-level schema.

Two notes on what this query does, in case you are comparing it against a simpler version:

• CREATED_DATE_UTC and MODIFY_DATE_UTC do not exist in INFORMATION_SCHEMA at all, so the query joins sys.objects to get them.
• TABLE_DDL, TABLE_COMMENT and COLUMN_COMMENT are what Solid uses to build view lineage and to read your existing documentation. They come from OBJECT_DEFINITION() and sys.extended_properties.

DECLARE @DatabaseName NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);

-- Cursor to iterate over each database, excluding system databases
DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name
FROM sys.databases
WHERE state_desc = N'ONLINE'
  AND database_id > 4; -- Exclude master, model, msdb, tempdb

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DatabaseName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Build dynamic SQL to query INFORMATION_SCHEMA in each database
    SET @SQL = N'
USE ' + QUOTENAME(@DatabaseName) + N';
SELECT
    DB_NAME()                                   AS TABLE_CATALOG,
    c.TABLE_SCHEMA                              AS TABLE_SCHEMA,
    t.TABLE_TYPE                                AS TABLE_TYPE,
    c.TABLE_NAME                                AS TABLE_NAME,
    c.COLUMN_NAME                               AS COLUMN_NAME,
    c.ORDINAL_POSITION                          AS ORDINAL_POSITION,
    c.DATA_TYPE                                 AS DATA_TYPE,
    c.CHARACTER_MAXIMUM_LENGTH                  AS CHARACTER_MAXIMUM_LENGTH,
    c.NUMERIC_PRECISION                         AS NUMERIC_PRECISION,
    c.NUMERIC_SCALE                             AS NUMERIC_SCALE,
    c.IS_NULLABLE                               AS IS_NULLABLE,
    c.COLUMN_DEFAULT                            AS COLUMN_DEFAULT,
    -- Dates live only in sys.objects; INFORMATION_SCHEMA has no equivalent.
    CONVERT(varchar(33), o.create_date, 126)    AS CREATED_DATE_UTC,
    CONVERT(varchar(33), o.modify_date, 126)    AS MODIFY_DATE_UTC,
    CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN ''TRUE'' ELSE ''FALSE'' END AS IS_PK,
    CASE WHEN ic.column_id  IS NOT NULL THEN ''TRUE'' ELSE ''FALSE'' END AS IS_INDEX,
    rc.TABLE_ROW_COUNT                          AS TABLE_ROW_COUNT,
    -- View definitions: Solid parses these to build lineage between views and tables.
    OBJECT_DEFINITION(o.object_id)              AS TABLE_DDL,
    -- Existing documentation, if your team maintains it.
    CAST(tp.value AS NVARCHAR(MAX))             AS TABLE_COMMENT,
    CAST(cp.value AS NVARCHAR(MAX))             AS COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t
       ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
      AND t.TABLE_NAME   = c.TABLE_NAME
INNER JOIN sys.schemas sch
       ON sch.name = c.TABLE_SCHEMA
INNER JOIN sys.objects o
       ON o.schema_id = sch.schema_id
      AND o.name      = c.TABLE_NAME
      AND o.type IN (''U'', ''V'')
INNER JOIN sys.columns sc
       ON sc.object_id = o.object_id
      AND sc.name      = c.COLUMN_NAME
LEFT JOIN (
    SELECT kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.COLUMN_NAME
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
           ON tc.CONSTRAINT_NAME   = kcu.CONSTRAINT_NAME
          AND tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
          AND tc.CONSTRAINT_TYPE   = ''PRIMARY KEY''
) pk ON pk.TABLE_SCHEMA = c.TABLE_SCHEMA
    AND pk.TABLE_NAME   = c.TABLE_NAME
    AND pk.COLUMN_NAME  = c.COLUMN_NAME
LEFT JOIN (SELECT DISTINCT object_id, column_id FROM sys.index_columns) ic
       ON ic.object_id = o.object_id
      AND ic.column_id = sc.column_id
LEFT JOIN (
    SELECT object_id, SUM(row_count) AS TABLE_ROW_COUNT
    FROM sys.dm_db_partition_stats
    WHERE index_id IN (0, 1)
    GROUP BY object_id
) rc ON rc.object_id = o.object_id
LEFT JOIN sys.extended_properties tp
       ON tp.major_id = o.object_id
      AND tp.minor_id = 0
      AND tp.name     = ''MS_Description''
LEFT JOIN sys.extended_properties cp
       ON cp.major_id = o.object_id
      AND cp.minor_id = sc.column_id
      AND cp.name     = ''MS_Description''
ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME, c.ORDINAL_POSITION;';

    -- Execute the dynamic SQL
    EXEC sys.sp_executesql @SQL;

    FETCH NEXT FROM db_cursor INTO @DatabaseName;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;

Notes:

• Row counts come from sys.dm_db_partition_stats, which is SQL Server's own maintained estimate. This is intentional — it is far cheaper than COUNT(*) across every table and accurate enough for Solid's purposes.
• TABLE_COMMENT and COLUMN_COMMENT will be empty if your team has not filled in the Description field in SSMS. That is fine and not an error. Where descriptions do exist, they are the single highest-quality input Solid receives, so it is worth checking whether your databases have them.

Relationships and Foreign Keys

Run this in addition to the script above. It produces the file Solid reads to import relationships between tables.

DECLARE @DatabaseName NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);

DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name
FROM sys.databases
WHERE state_desc = N'ONLINE'
  AND database_id > 4;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DatabaseName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = N'
USE ' + QUOTENAME(@DatabaseName) + N';
SELECT
    DB_NAME()                AS pk_database_name,
    rs.name                  AS pk_schema_name,
    rt.name                  AS pk_table_name,
    rc.name                  AS pk_column_name,
    DB_NAME()                AS fk_database_name,
    ps.name                  AS fk_schema_name,
    pt.name                  AS fk_table_name,
    pc.name                  AS fk_column_name,
    fkc.constraint_column_id AS key_sequence,
    fk.name                  AS fk_name
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN sys.tables  pt ON pt.object_id = fkc.parent_object_id
INNER JOIN sys.schemas ps ON ps.schema_id = pt.schema_id
INNER JOIN sys.columns pc ON pc.object_id = fkc.parent_object_id
                         AND pc.column_id = fkc.parent_column_id
INNER JOIN sys.tables  rt ON rt.object_id = fkc.referenced_object_id
INNER JOIN sys.schemas rs ON rs.schema_id = rt.schema_id
INNER JOIN sys.columns rc ON rc.object_id = fkc.referenced_object_id
                         AND rc.column_id = fkc.referenced_column_id
ORDER BY fk.name, fkc.constraint_column_id;';

    EXEC sys.sp_executesql @SQL;
    FETCH NEXT FROM db_cursor INTO @DatabaseName;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;

The lowercase column names here are deliberate and must be preserved — see File naming for how to save this result set, which is also load-bearing.

If a relationship exists in practice but was never declared as a foreign key, Solid can still infer it from query history. That is one of the reasons the query log below matters.

Option 2: Using sys Catalog Views

The sys schema provides a more detailed, SQL Server-specific view of the metadata. Use this only if you cannot run Option 1 — it returns fewer of the fields Solid uses, so Option 1 is strongly preferred. Note that the aliases below are chosen to match what Solid expects, which differs from the natural sys column names.

DECLARE @DatabaseName nvarchar(128);
DECLARE @SQL nvarchar(max);

DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name
FROM sys.databases
WHERE state_desc = N'ONLINE'
  AND database_id > 4; -- Exclude master, model, msdb, tempdb

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DatabaseName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = N'
SELECT
    ' + QUOTENAME(@DatabaseName, '''') + N' AS TABLE_CATALOG,
    s.name                                   AS TABLE_SCHEMA,
    CASE o.type WHEN ''V'' THEN ''VIEW'' ELSE ''BASE TABLE'' END AS TABLE_TYPE,
    o.name                                   AS TABLE_NAME,
    c.name                                   AS COLUMN_NAME,
    c.column_id                              AS ORDINAL_POSITION,
    ty.name                                  AS DATA_TYPE,
    -- sys.columns.max_length is in BYTES, not characters. nchar/nvarchar are
    -- 2 bytes per character, so this must be halved (max-sized columns keep -1).
    CASE
        WHEN c.max_length = -1 THEN -1
        WHEN ty.name IN (''nchar'', ''nvarchar'') THEN c.max_length / 2
        ELSE c.max_length
    END                                       AS CHARACTER_MAXIMUM_LENGTH,
    c.precision                              AS NUMERIC_PRECISION,
    c.scale                                  AS NUMERIC_SCALE,
    CASE c.is_nullable WHEN 1 THEN ''YES'' ELSE ''NO'' END AS IS_NULLABLE,
    CONVERT(varchar(33), o.create_date, 126) AS CREATED_DATE_UTC,
    CONVERT(varchar(33), o.modify_date, 126) AS MODIFY_DATE_UTC,
    OBJECT_DEFINITION(o.object_id)           AS TABLE_DDL,
    ty.collation_name                        AS COLLATION_NAME,
    u.name                                   AS SCHEMA_OWNER
FROM ' + QUOTENAME(@DatabaseName) + N'.sys.objects AS o
INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.columns AS c
    ON o.object_id = c.object_id
INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.types AS ty
    ON c.user_type_id = ty.user_type_id
INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas AS s
    ON o.schema_id = s.schema_id
LEFT JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.database_principals AS u
    ON s.principal_id = u.principal_id
WHERE o.type IN (N''U'', N''V'') -- User tables and views
ORDER BY o.name, c.column_id;';

    EXEC sys.sp_executesql @SQL;
    FETCH NEXT FROM db_cursor INTO @DatabaseName;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;

Query Log Extraction

Capturing query history is crucial for understanding data usage. SQL Server offers several methods for this.

Method 1: Query Store (Recommended)

If enabled on your databases, the Query Store is the most reliable method. It captures a detailed history of queries, execution plans, and performance statistics.

Prerequisites:

• Query Store must be enabled on each database you want to monitor
• Enable Query Store: ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON;

📘

Is it already on?

Query Store is enabled by default on Azure SQL Managed Instance and Azure SQL Database, so there is often history waiting for you with no setup at all. It is disabled by default on self-hosted SQL Server 2016 through 2022. If you have to enable it, note that history only accumulates from that moment forward — so turn it on as early as possible before your Solid deployment.

Extraction Script:

Run this script on each database where Query Store is enabled:

SELECT
    q.query_id,
    q.query_text_id,
    qt.query_sql_text,
    rs.avg_duration,
    rs.execution_type_desc,
    rs.count_executions,
    rs.last_execution_time
FROM sys.query_store_query AS q
INNER JOIN sys.query_store_query_text AS qt
    ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
ORDER BY rs.last_execution_time DESC;

❗️

The column is count_executions, not execution_count

Query Store's runtime stats view names this column count_executions. (Confusingly, the DMV in Method 2 does call it execution_count — that one is correct as written.) Using the wrong name fails with Msg 207: Invalid column name.

Benefits:

• Persistent query history (survives server restarts)
• Detailed execution statistics
• Performance metrics and query plans

Recommended settings. If you are configuring Query Store for a Solid deployment, check that history is not being discarded before Solid collects it:

SELECT  actual_state_desc,
        query_capture_mode_desc,
        current_storage_size_mb,
        max_storage_size_mb,
        stale_query_threshold_days,
        size_based_cleanup_mode_desc
FROM sys.database_query_store_options;

• query_capture_mode_desc of AUTO discards infrequent and inexpensive queries. Ad-hoc analytical queries are often exactly that, and they are among the most informative for Solid. ALL is preferred where the storage budget allows.
• If current_storage_size_mb is close to max_storage_size_mb and size_based_cleanup_mode_desc is AUTO, older queries are already being evicted regardless of stale_query_threshold_days. Raise the ceiling before relying on the retention window.

Method 2: Dynamic Management Views (DMVs)

DMVs provide information about cached query plans and execution statistics from memory.

Note: This information is volatile and can be evicted from the cache if there is memory pressure.

Extraction Script:

Run this script at the instance level:

SELECT
    t.text AS query_text,
    s.execution_count,
    s.total_elapsed_time,
    s.last_elapsed_time,
    s.total_worker_time,
    s.total_logical_reads,
    s.total_logical_writes,
    s.creation_time,
    s.last_execution_time
FROM sys.dm_exec_query_stats AS s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
ORDER BY s.last_execution_time DESC;

Limitations:

• Query history is lost on server restart
• Cache can be cleared due to memory pressure
• Limited historical data

Method 3: Other Options

SQL Server Trace / Extended Events:

• Can be configured to capture detailed query activity to a file
• Requires setup and management
• More complex but provides comprehensive logging

Third-Party Tools:

• ApexSQL Log
• Redgate SQL Log Rescue
• Can read transaction logs to reconstruct query history

Exporting Results

After running the extraction scripts, export the results for upload to Solid.

File naming

❗️

One naming rule is required, not a suggestion

Relationships files must end in fks_to_add.csv. Any other ending is processed as ordinary table metadata — no error is raised, but none of your relationships will be imported.

Only the relationships-file ending is checked by Solid's ingestion. The metadata and query-history namings are a convention that keeps files organized and identifiable to a human — which storage location you upload each file to (as directed by your Solid contact) is what actually determines how it's processed, not the filename.

If you collected from more than one database, prefix each file with the database name and keep the files separate — do not merge them.

Exporting to CSV

In SQL Server Management Studio (SSMS):

1. Run the query
2. Right-click on the results grid
3. Select Save Results As
4. Choose CSV format

Using sqlcmd:

🚧

View definitions and descriptions contain commas and line breaks

A plain sqlcmd -s"," export corrupts any row whose TABLE_DDL or comment spans multiple lines: the row splits across records and the file no longer parses. Two flags matter. -y 0 prevents sqlcmd from truncating long text at 256 characters, and the values must be quoted properly. The simplest robust approach is to have the query emit ready-quoted CSV, as below. SSMS Save Results As handles the quoting for you and is the easier route if it is available.

sqlcmd -S ServerName -d master -U solid_user -P Password -y 0 \
       -i extract_metadata.sql -o SWBI_Datawarehouse_metadata.csv

To have the query produce correctly quoted CSV, wrap each field as '"' + REPLACE(ISNULL(CAST(col AS NVARCHAR(MAX)),''),'"','""') + '"' and concatenate with ,. Add the header row yourself. If you would rather not modify the query, export from SSMS instead.

Compress and upload:

• Archive all CSV files (ZIP or GZIP)
• Upload to the Solid Azure Storage container or as directed by your Solid administrator

Permissions Needed

These permissions cover the full setup. VIEW SERVER STATE can be omitted if you are using Query Store (Method 1) for query history — which is the recommended approach.

Troubleshooting

Permission Issues

If you encounter "permission denied" errors:

1. Verify server-level permissions:

   SELECT * FROM sys.server_permissions WHERE grantee_principal_id = SUSER_ID('solid_user');

2. Verify database-level permissions:

   USE [YourDatabase];
   SELECT * FROM sys.database_permissions WHERE grantee_principal_id = USER_ID('solid_user');

3. Ensure the user exists in each database:

   SELECT name FROM sys.database_principals WHERE name = 'solid_user';

Remember that steps 3 and 4 of the setup script are per-database. Cannot open database "X" requested by the login almost always means the login exists but the database user was never created in that database.

Metadata Extraction Issues

The extraction fails or Solid reports missing columns. The column aliases in the scripts above are part of the interface. Confirm your export's header row still contains TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME and DATA_TYPE spelled exactly that way.

Rows appear broken or shifted in the CSV. A view definition or description containing a comma or line break was exported without quoting — see Exporting to CSV.

Empty rows for schemas with no tables. Use the INNER JOIN form shown in Option 1. Joining outward from INFORMATION_SCHEMA.SCHEMATA returns a row for every empty system schema (db_owner, db_datareader, guest, INFORMATION_SCHEMA), which Solid then imports as empty objects.

TABLE_DDL is empty for every view, and no error was raised. This is almost always a missing GRANT VIEW DEFINITION. Without that permission OBJECT_DEFINITION() returns NULL rather than failing — the export succeeds, the row count looks right, and the DDL column is simply blank. Because Solid derives view-to-table lineage from that column, the import completes with no lineage and nothing in the export signals why. Check it directly — connect as solid_user for this, not as an administrator, since the whole point is what that specific login can see:

   USE [YourDatabase];
   SELECT COUNT(*) AS views_total,
          SUM(CASE WHEN OBJECT_DEFINITION(object_id) IS NULL THEN 1 ELSE 0 END) AS views_missing_ddl
   FROM sys.views;

If views_missing_ddl equals views_total, re-run GRANT VIEW DEFINITION TO solid_user; in that database — remember it is per-database, so it is easy to grant on one and miss another. If only some views are missing DDL, those are likely WITH ENCRYPTION views, which return NULL by design and cannot be exported; see below.

A few specific views have no DDL while the rest are fine. Views created WITH ENCRYPTION never expose their definition, to anyone, at any permission level. Nothing can be done about this and no grant changes it. Those views still import with all their column metadata; only their lineage is missing. Confirm which ones:

   USE [YourDatabase];
   SELECT SCHEMA_NAME(schema_id) AS [schema], name
   FROM sys.views
   WHERE OBJECT_DEFINITION(object_id) IS NULL;

Query Store Issues

If Query Store data is not available:

1. Check if Query Store is enabled:

   SELECT name, is_query_store_on FROM sys.databases;

2. Enable Query Store:

   ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON;

3. Configure Query Store settings:

   ALTER DATABASE [YourDatabase] 
   SET QUERY_STORE (
       OPERATION_MODE = READ_WRITE,
       DATA_FLUSH_INTERVAL_SECONDS = 900,
       INTERVAL_LENGTH_MINUTES = 60,
       MAX_STORAGE_SIZE_MB = 1000,
       QUERY_CAPTURE_MODE = ALL,
       CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30)
   );

Note the parentheses around STALE_QUERY_THRESHOLD_DAYS: it is nested inside CLEANUP_POLICY, and writing it as a top-level option is a syntax error.

Connection Issues

If you can't connect to SQL Server:

1. Verify SQL Server authentication mode (must allow SQL Server authentication)
2. Check firewall rules and network connectivity
3. Confirm the login is enabled:

   SELECT name, is_disabled FROM sys.sql_logins WHERE name = 'solid_user';

Security Notes

• Use strong passwords for the solid_user login
• Grant minimum necessary permissions - avoid granting sysadmin or db_owner roles
• Rotate passwords regularly according to your organization's policies. solid_user must use SQL Server authentication (not Windows/Entra ID) — this page's setup script creates it as a SQL login, since Solid connects with a login and password, not through your domain
• Monitor login activity through SQL Server audit logs
• Disable the account when not in use for extended periods
• Use dedicated service accounts rather than personal accounts
• Restrict network access to SQL Server ports (default 1433)
• Enable encryption for SQL Server connections (SSL/TLS)

Optionally, make the read-only intent explicit and auditable. db_datareader already grants no write access, but this reassures reviewers:

DENY INSERT, UPDATE, DELETE, ALTER, EXECUTE TO solid_user;

SQL Server Version Compatibility

This guide is compatible with:

• SQL Server 2016 and later (for Query Store support)
• SQL Server 2012 and later (for basic metadata extraction)
• Azure SQL Database
• Azure SQL Managed Instance

Note: Query Store is available in SQL Server 2016+ and all Azure SQL offerings.

Differences worth knowing

Additional Resources

• SQL Server Query Store
• SQL Server DMVs
• SQL Server Permissions
• INFORMATION_SCHEMA Views