Masking Sensitive Data in Azure SQL

DataMasking.sql

------------------------------------------------------------
-- Dynamic Data Masking demo (SSN, Email, Phone)
-- Idempotent all-in-one script
------------------------------------------------------------

------------------------------------------------------------
-- Step 1: Create user
------------------------------------------------------------
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'AppUser')
BEGIN
    CREATE USER AppUser WITHOUT LOGIN;
END
GO

------------------------------------------------------------
-- Step 2: Create table
------------------------------------------------------------
IF OBJECT_ID('dbo.Customers', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.Customers
    (
        Id        INT IDENTITY(1,1) PRIMARY KEY,
        FullName  NVARCHAR(200) NOT NULL,
        SSN       CHAR(11) NOT NULL,
        Email     NVARCHAR(256) NOT NULL,
        Phone     NVARCHAR(20) NOT NULL
    );
END
GO

------------------------------------------------------------
-- Step 3: Seed data (only if empty)
------------------------------------------------------------
IF NOT EXISTS (SELECT 1 FROM dbo.Customers)
BEGIN
    INSERT INTO dbo.Customers (FullName, SSN, Email, Phone) VALUES
        ('Alex Johnson', '123-45-6789', 'alex@example.com', '555-123-4567'),
        ('Maria Lopez',  '987-65-4321', 'maria@example.com', '555-987-6543');
END
GO

------------------------------------------------------------
-- Step 4: Ensure masking exists (drop only if currently masked)
------------------------------------------------------------

-- SSN
IF EXISTS
(
    SELECT 1
    FROM sys.columns
    WHERE object_id = OBJECT_ID('dbo.Customers')
      AND name = 'SSN'
      AND is_masked = 1
)
BEGIN
    ALTER TABLE dbo.Customers
    ALTER COLUMN SSN DROP MASKED;
END
GO

ALTER TABLE dbo.Customers
ALTER COLUMN SSN
ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)');
GO

-- Email
IF EXISTS
(
    SELECT 1
    FROM sys.columns
    WHERE object_id = OBJECT_ID('dbo.Customers')
      AND name = 'Email'
      AND is_masked = 1
)
BEGIN
    ALTER TABLE dbo.Customers
    ALTER COLUMN Email DROP MASKED;
END
GO

ALTER TABLE dbo.Customers
ALTER COLUMN Email
ADD MASKED WITH (FUNCTION = 'email()');
GO

-- Phone
IF EXISTS
(
    SELECT 1
    FROM sys.columns
    WHERE object_id = OBJECT_ID('dbo.Customers')
      AND name = 'Phone'
      AND is_masked = 1
)
BEGIN
    ALTER TABLE dbo.Customers
    ALTER COLUMN Phone DROP MASKED;
END
GO

ALTER TABLE dbo.Customers
ALTER COLUMN Phone
ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XXX-",4)');
GO

------------------------------------------------------------
-- Step 5: Grant permissions (safe to re-run)
------------------------------------------------------------
GRANT SELECT ON dbo.Customers TO AppUser;
GO

------------------------------------------------------------
-- Step 6: Demo queries
------------------------------------------------------------

-- Admin view
SELECT * FROM dbo.Customers;
GO

-- Masked view
EXECUTE AS USER = 'AppUser';
SELECT * FROM dbo.Customers;
REVERT;
GO