Classify Azure SQL Columns as Restricted, Confidential or Internal

What you will have at the end

One T-SQL file, classify_columns.sql. It holds 17 stated rules in a table (15 match a column’s name, 2 match the shape of its values), a view that gives every column the highest level any matching rule assigns, three built-in checks, and a step that writes the labels into the database’s own sensitivity metadata only when all three checks pass. It creates no database: you run it inside an empty test database you own. We ran it twice: once with three planted faults, once clean.

The test table has the 21 column names of the public FEC individual-contributions file layout. Its 1,000 rows are generated by the script, so no real person’s value appears anywhere. We ran it on SQL Server 2022 in a container, not on an Azure SQL Database. The sensitivity-classification statements and views it relies on are documented for both, and we link them below. We did not run the file on Azure SQL, and we did not check the rest of its T-SQL against Azure SQL’s documentation.

Step 1. Write the rules down as data

A rule is a row: an id, a level, a kind, a pattern and a reason. A NAME rule matches the column’s name. A VALUE rule matches the values in the column, and fires when at least a stated share of the sampled values match. Patterns use T-SQL LIKE, where _ means any single character, so a literal underscore is written [_] (Microsoft: LIKE (read 2026-10-20)). LIKE comparisons are affected by collation, so a case-sensitive database matches differently (same page). The levels live in their own table, and a rule’s level is a foreign key to it, so a misspelled level cannot be inserted.

SQL: the levels and the rules

-- 1. The levels, and the stated rules. A rule's level must be one of the three levels (foreign key).
CREATE TABLE classify.levels (level_name varchar(12) PRIMARY KEY, level_rank int NOT NULL UNIQUE);
INSERT INTO classify.levels VALUES ('restricted', 3), ('confidential', 2), ('internal', 1);

CREATE TABLE classify.rules (
  rule_id char(3) PRIMARY KEY,
  level_name varchar(12) NOT NULL REFERENCES classify.levels (level_name),
  kind varchar(5) NOT NULL CHECK (kind IN ('NAME', 'VALUE')),
  pattern nvarchar(200) NOT NULL,   -- a T-SQL LIKE pattern; write a literal underscore as [_]
  min_share decimal(5,4) NULL,      -- VALUE rules: share of sampled values that must match
  reason nvarchar(200) NOT NULL,
  -- a VALUE rule without a share would never fire; a NAME rule takes none.
  -- IS NOT NULL matters: a CHECK lets a NULL comparison (unknown) through.
  CONSTRAINT rules_min_share CHECK ((kind = 'NAME' AND min_share IS NULL)
                                 OR (kind = 'VALUE' AND min_share IS NOT NULL AND min_share > 0 AND min_share <= 1)));
INSERT INTO classify.rules VALUES
  ('R01', 'restricted',   'NAME',  'NAME',               NULL, 'Names a person'),
  ('R02', 'restricted',   'VALUE', '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]', 0.01, 'Values shaped like a US Social Security number'),
  ('R03', 'restricted',   'VALUE', '%_@_%.__%',          0.01, 'Values shaped like an email address'),
  ('R04', 'confidential', 'NAME',  'CITY',               NULL, 'Location of a person'),
  ('R05', 'confidential', 'NAME',  'STATE',              NULL, 'Location of a person'),
  ('R06', 'confidential', 'NAME',  'ZIP[_]CODE',         NULL, 'Location of a person'),
  ('R07', 'confidential', 'NAME',  'EMPLOYER',           NULL, 'Where a person works'),
  ('R08', 'confidential', 'NAME',  'OCCUPATION',         NULL, 'What a person does'),
  ('R09', 'confidential', 'NAME',  'TRANSACTION[_]AMT',  NULL, 'An amount tied to a person'),
  ('R10', 'confidential', 'NAME',  'MEMO[_]TEXT',        NULL, 'Free text can hold anything'),
  ('R11', 'internal',     'NAME',  '%[_]ID',             NULL, 'System identifier'),
  ('R12', 'internal',     'NAME',  '%[_]TP',             NULL, 'Type code'),
  ('R13', 'internal',     'NAME',  '%[_]NUM',            NULL, 'Reference number'),
  ('R14', 'internal',     'NAME',  '%[_]IND',            NULL, 'Indicator flag'),
  ('R15', 'internal',     'NAME',  '%[_]CD',             NULL, 'Code'),
  ('R16', 'internal',     'NAME',  'TRANSACTION[_]PGI',  NULL, 'Code'),
  ('R17', 'internal',     'NAME',  'TRANSACTION[_]DT',   NULL, 'Date of the transaction');
GO

The same rules, as the file stores them:

These levels and rules are ours, written for this demo. They are not a legal standard, and your policy will differ. The point is that they are rows anyone can read, argue with and change, and that every label can be traced to the rule that produced it.

Step 4. Run it with planted faults, then clean

With PLANT=1 the file plants three faults before it classifies:

SQL: the planted faults

-- 2. TEST ONLY: plant three faults when PLANT=1.
IF $(PLANT) = 1
BEGIN
  -- fault 1: an "internal" ID column that holds Social-Security-shaped values
  UPDATE fec.contributions SET TRAN_ID = '000-00-' + RIGHT('0000' + CAST(SUB_ID AS varchar(4)), 4);
  -- fault 2: a typo in a rule's pattern, so it matches no column
  UPDATE classify.rules SET pattern = 'ZIPCODE' WHERE rule_id = 'R06';
  -- fault 3: a new column that no rule covers
  ALTER TABLE fec.contributions ADD BIRTH_DT date NULL;
END;
GO

The run, with sqlcmd (-b makes sqlcmd stop at the first error, so the statements after the failed check did not run):

sqlcmd -S <server> -d <test database> -U <user> -P <password> -b -W -s "|" -v PLANT=1 -i classify_columns.sql

Output from our run: the classification with the three faults in place

COLUMN_NAME|name_level|value_level|level|rules
-----------|----------|-----------|-----|-----
CMTE_ID|internal|NULL|internal|R11
AMNDT_IND|internal|NULL|internal|R14
RPT_TP|internal|NULL|internal|R12
TRANSACTION_PGI|internal|NULL|internal|R16
IMAGE_NUM|internal|NULL|internal|R13
TRANSACTION_TP|internal|NULL|internal|R12
ENTITY_TP|internal|NULL|internal|R12
NAME|restricted|NULL|restricted|R01
CITY|confidential|NULL|confidential|R04
STATE|confidential|NULL|confidential|R05
ZIP_CODE|NULL|NULL|NULL|NULL
EMPLOYER|confidential|NULL|confidential|R07
OCCUPATION|confidential|NULL|confidential|R08
TRANSACTION_DT|internal|NULL|internal|R17
TRANSACTION_AMT|confidential|NULL|confidential|R09
OTHER_ID|internal|NULL|internal|R11
TRAN_ID|internal|restricted|restricted|R02,R11
FILE_NUM|internal|NULL|internal|R13
MEMO_CD|internal|NULL|internal|R15
MEMO_TEXT|confidential|NULL|confidential|R10
SUB_ID|internal|NULL|internal|R11
BIRTH_DT|NULL|NULL|NULL|NULL

ZIP_CODE and BIRTH_DT get no level. TRAN_ID is restricted because rule R02 found Social-Security-shaped values in a column whose name said internal. The checks and the findings:

Output from our run: three of three checks failed, nothing written

check_name|problems
----------|--------
every column gets a level|2
every name rule matches a column|1
no value rule outranks the column name|1
check_name|item|detail
----------|----|------
every column gets a level|BIRTH_DT|no rule matches this column
every column gets a level|ZIP_CODE|no rule matches this column
every name rule matches a column|R06|pattern ZIPCODE matches no column
no value rule outranks the column name|TRAN_ID|name rules say internal, values say restricted
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Msg 50001, Level 16, State 1, Server 5e56364b6284, Procedure classify.apply, Line 11
Checks failed: 3 of 3. No label written.

The two Warning lines are SQL Server’s notice that an aggregate skipped NULLs. They are harmless here. The error is the file’s own: classify.apply refuses to write while any check has a problem, and sqlcmd stopped there. The labeling queries after it did not run, so the database has no sensitivity labels.

Then the same file with -v PLANT=0, in a second, empty test database. We ran it directly after the planted run:

Output from our run: clean, with the labels written (after the checks, the findings are empty; the two warnings are the same NULL notices)

check_name|problems
----------|--------
every column gets a level|0
every name rule matches a column|0
no value rule outranks the column name|0
Warning: Null value is eliminated by an aggregate or other SET operation.
check_name|item|detail
----------|----|------
Warning: Null value is eliminated by an aggregate or other SET operation.
column_name|label|information_type
-----------|-----|----------------
CMTE_ID|internal|rules R11
AMNDT_IND|internal|rules R14
RPT_TP|internal|rules R12
TRANSACTION_PGI|internal|rules R16
IMAGE_NUM|internal|rules R13
TRANSACTION_TP|internal|rules R12
ENTITY_TP|internal|rules R12
NAME|restricted|rules R01
CITY|confidential|rules R04
STATE|confidential|rules R05
ZIP_CODE|confidential|rules R06
EMPLOYER|confidential|rules R07
OCCUPATION|confidential|rules R08
TRANSACTION_DT|internal|rules R17
TRANSACTION_AMT|confidential|rules R09
OTHER_ID|internal|rules R11
TRAN_ID|internal|rules R11
FILE_NUM|internal|rules R13
MEMO_CD|internal|rules R15
MEMO_TEXT|confidential|rules R10
SUB_ID|internal|rules R11
label|columns_labeled
-----|---------------
confidential|7
internal|13
restricted|1

All three checks have zero problems and the findings list is empty. 21 labels were written: 1 restricted, 7 confidential and 13 internal. Each label’s information type records the rule ids that produced it, so a reviewer can go from a label to its reason.

Tested on

Use it on your own schema

The file creates no database. Run with -b, it refuses to run in master or another system database, and every schema, table, view and procedure is created with a plain CREATE, so if a name is already in use the run stops at that statement instead of replacing what is there. For the test run, use an empty test database you own, which you choose with sqlcmd’s -d. To point it at your own tables, connect with -d to the database that holds them, remove the CREATE SCHEMA fec statement and the steps marked 0 and 2 in the file (the generated rows and the planted faults), and change 'fec' in the inventory view to your schema. If that database already has a schema called classify, replace classify in the CREATE SCHEMA classify line and every classify. in the file with another schema name. Replace the rows in classify.rules with your own policy: the table refuses a VALUE rule without a share above 0 and up to 1, and a NAME rule with one. Once the planted-faults step (marked 2 in the file) is gone, the -v PLANT option is no longer needed. To run it again in the same database, drop the classify objects first. Because the write step (step 5 above) overwrites an existing classification on a column, look at sys.sensitivity_classifications first.

When this is not enough

• It measures nothing about accuracy. The table is generated and the rules were written to fit its column names, so the run shows the mechanism and the checks, not how many real columns the rules would label correctly or wrongly.
• Names and shapes only. It finds a column named for a person or holding values shaped like a Social Security number or email address. A column with an unhelpful name and unusual values is missed, and a column with a matching name can hold something else. Each pattern is a guess a data owner has to confirm.
• The value scan samples. It reads at most 1,000 non-NULL values per column in no stated order. Our table has 1,000 rows, so we did not test sampling on a larger one. The scan also reads real values, so it needs read access to them.
• The checks test the rules, not the labels. They catch an uncovered column, a rule that matches nothing and a value-versus-name conflict. They cannot tell whether a level is the right level.
• One schema, one database, run by hand. Nothing here is scheduled, versioned or reviewed, and nobody signs the result off.
• A label is a label. Writing it into sys.sensitivity_classifications records the classification. This file does not mask, encrypt or restrict anything.