Data Governance Meets SQL: Unlocking Insights with Microsoft Purview

In data-rich enterprises, managing and governing SQL data is no longer optional. Teams focused on performance, backup, indexing, and analytics routines often overlook the governance layer entirely. Without it, the risks include data quality issues, compliance failures, opaque lineage, uncontrolled access, and wasted analytics effort. This article covers how to apply data governance principles across SQL environments using Microsoft Purview, with working SQL examples, governance patterns, and a hands-on workshop for Azure SQL and on-premises SQL Server connected via Azure Arc.

For every SQL database in an estate, governance addresses six concerns that performance tuning and backups alone cannot solve:

SQL systems are often the foundation of analytic, reporting, and operational workloads. Weak governance means downstream analytics in Power BI, dashboards, and ML models receive unreliable results. Fixing poor data governance after the fact is significantly more costly than building it in from the start.

Microsoft Purview is Microsoft’s unified data governance platform. Data sources (cloud and on-premises) are registered in Purview, their metadata is scanned and cataloged, data assets are classified and labeled, access policies are defined, lineage is built, and business glossaries are maintained. The result is a governed, discoverable, and trustworthy data estate across all connected sources.

Key capabilities relevant to SQL environments:

Consider a table in an Azure SQL Database:

CREATE TABLE dbo.Customer (
    CustomerID   INT           PRIMARY KEY,
    FirstName    NVARCHAR(100),
    LastName     NVARCHAR(100),
    Email        NVARCHAR(255),
    CreditCard   CHAR(16),
    CreatedDate  DATETIME2
);
GO

When Purview scans this database, it identifies columns matching known sensitive data patterns and recommends classifications. The CreditCard column is identified and classified as “Credit Card Number” with a “Highly Confidential” sensitivity label. After tagging in the Purview catalog, a protection policy restricts read access to users in a designated security group — all others are blocked from the labeled asset.

The classification taxonomy in Purview aligns with the sensitivity labels defined in the Microsoft 365 Compliance Center. When the same labels are used in Purview and in SQL Server’s native classification feature, the label inventory is consistent across the entire data estate. For SQL Server on-premises instances, the SSMS Data Classification pane can import Purview labels directly, creating a single source of truth for sensitivity labeling.

On-premises SQL Server 2022 instances connected via Azure Arc can be governed through Purview access policies, allowing data owners rather than DBAs to assign access permissions. The DBA still manages the SQL Server instance, but domain-level access decisions for cataloged assets flow through Purview rather than requiring individual GRANT statements on every instance.

The high-level setup requires registering the SQL Server source in Purview, enabling data policy enforcement for the source, and creating a policy that grants the appropriate group access to the target assets. The Purview managed identity also needs permissions in SQL Server to scan metadata:

-- Grant the Purview managed identity read access for metadata scanning
-- Replace  with the managed identity name from the Purview account
USE YourDatabase;
GO

CREATE USER [] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [];
GO

Consider a SQL view that aggregates customer and order data:

CREATE VIEW dbo.vw_SalesSummary AS
SELECT
    c.CustomerID,
    c.LastName,
    SUM(o.Amount) AS TotalSpent
FROM dbo.Customer c
JOIN dbo.Orders   o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.LastName;
GO

When Purview scans the database with lineage extraction enabled, it maps the dependency from dbo.vw_SalesSummary to dbo.Customer and dbo.Orders. This lineage appears in the Purview asset view for the view, showing upstream sources and any downstream consumers.

The practical value is in impact analysis: if the schema of dbo.Orders changes (the Amount column is renamed or its data type changes), Purview lineage immediately shows which views, stored procedures, and downstream Power BI reports depend on that column. Without lineage, identifying all affected objects requires manual investigation across the estate.

Purview organizes governed data assets into domains and data products. A domain represents a business area — “Customer and Sales” — and contains data products that package related assets together for consumption by analytics teams.

A data product called “Sales Summary Dataset” inside the “Customer and Sales” domain would include the dbo.vw_SalesSummary view and the underlying tables, with business glossary terms (“CustomerID”, “TotalSpent”, “EmailAddress”) linked to the relevant columns. Analytics teams browsing Purview see the data product as ready, curated, and trusted — without needing to understand the underlying schema or trace which tables feed which views.

Glossary terms link business language to technical column names. A column named Amt becomes discoverable and understandable when linked to a glossary term “TotalSpent (USD, net of returns)” with an owner, a definition, and a reference to the upstream source.

The following steps walk through a complete Purview governance setup targeting an Azure SQL Database. The same steps apply to an on-premises SQL Server 2022 instance registered via Azure Arc, with the Arc registration completed first.

Prerequisites: an Azure subscription, an Azure SQL Database or Azure Arc-connected SQL Server 2022 instance, and a Microsoft Purview account in the same Azure tenant.

Step 1: Register the SQL Data Source

In the Purview portal, navigate to Data Map, Sources, Register. Choose Azure SQL Database (or SQL Server on Azure Arc for on-premises instances) and complete the subscription, resource group, and server details. After registration, configure the scan credentials using the Purview managed identity and define the scan scope (full database or selected schemas).

Step 2: Grant Required Permissions in SQL

-- Grant the Purview managed identity read access for scanning
-- Replace  with the managed identity name shown in the Purview account
USE YourDatabase;
GO

CREATE USER [] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [];
GO

-- For stored procedure lineage, db_owner is required
-- Evaluate security implications before granting in production
-- ALTER ROLE db_owner ADD MEMBER [];

Step 3: Run the Metadata Scan

In Purview, under the registered source, select New Scan. Choose the target schemas or tables, select the scan rule set (which determines which classifiers run), and execute. After the scan completes, assets appear in the Data Map with their table names, column names, and any automatic classification recommendations applied.

Step 4: Curate Assets and Create Glossary Terms

In the Purview catalog, navigate to the discovered assets. For a key table such as dbo.Customer, add a description, accept or adjust classification recommendations (for example, confirming “Credit Card Number” on the CreditCard column), and assign a data owner. In the Glossary section, define business terms such as “CustomerID”, “TotalSpent”, and “EmailAddress” and link them to the corresponding columns in the asset view.

Step 5: Apply a Sensitivity Label and Protection Policy

Inside Purview, assign a sensitivity label such as “Highly Confidential” to a column flagged as sensitive. Define a protection policy specifying that only members of a designated security group can read items with that label. For supported data sources including Azure SQL and Arc-connected SQL Server 2022, Purview enforces this policy through its integration with Microsoft Entra ID and the Purview managed identity.

Step 6: Explore Lineage

For views and stored procedures, ensure lineage extraction is enabled in the scan configuration. After the scan completes, open the asset view for a view or stored procedure and inspect the Lineage tab. The graph shows upstream source tables and downstream consumers. Select any node to navigate to that asset and see its own lineage, classifications, and ownership.

Step 7: Build a Data Quality Rule (Optional)

In Purview under Data Quality (part of Unified Catalog), connect to the SQL source and define a rule — for example, no nulls in Customer.Email, or TotalSpent must be greater than or equal to zero. After the rule runs against the source data, the Data Quality dashboard shows pass rates, violation counts, and trend over time. Violations can trigger governance workflows to notify the data steward.

Step 8: Review Data Estate Health

In the Purview portal, navigate to Data Estate Health to review which domains have complete classification coverage, which assets are missing owners, and where data quality violations are concentrated. Use this view to prioritize the next governance sprint: onboard additional sources, expand domain coverage, or train data stewards on the classification workflow.