Security Exceptions Board

Status: Reviewed (spike v1)
Path: Optional spike #6
Stack: PowerShell (optional AD expand phase 2) → SQL permission snapshots/procs → Blazor Server
Builds on: SQL-05 Security & access hygiene; requires dbo.SqlInstance from SQL-SPIKE-01
Goal: Surface high-risk access findings (sysadmin roster/diff, orphans, xp_cmdshell, guest) on a Blazor exceptions board — no revoke from UI.

In scope (MVP)

Per active instance: server principals + sysadmin membership; orphaned DB users; key risk config flags
Generate SqlSecurityFinding rows with severity; open board = findings not in approved exception
Sysadmin diff vs prior snapshot (added/removed)
Blazor exceptions board + sysadmin roster
Reuse CollectRun (CollectorName = N'SecurityHygiene')
Never store password hashes or secrets in warehouse/UI

Out of scope (MVP)

Nested AD group expand (phase 2 — table stub OK)
Revoke/disable login from Blazor
Full permission matrix / every GRANT
Automatic spike #7 wiring (note hook: SecurityFinding)

A. Database objects

CREATE TABLE dbo.SqlLogin (
  SqlLoginId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  SqlInstanceId INT NOT NULL REFERENCES dbo.SqlInstance(SqlInstanceId),
  PrincipalName NVARCHAR(256) NOT NULL,
  PrincipalType NVARCHAR(60) NULL,       -- SQL_LOGIN / WINDOWS_LOGIN / WINDOWS_GROUP
  IsDisabled BIT NULL,
  IsSysadmin BIT NOT NULL CONSTRAINT DF_SqlLogin_Sysadmin DEFAULT (0),
  CreateDate DATETIME2(0) NULL,
  ModifyDate DATETIME2(0) NULL,
  IsPolicyChecked BIT NULL,
  IsExpirationChecked BIT NULL,
  LastSeenAt DATETIME2(0) NOT NULL,
  CONSTRAINT UQ_SqlLogin UNIQUE (SqlInstanceId, PrincipalName)
);

CREATE TABLE dbo.SqlLoginSysadminHistory (
  HistoryId BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  SqlInstanceId INT NOT NULL,
  PrincipalName NVARCHAR(256) NOT NULL,
  ChangeType VARCHAR(16) NOT NULL,       -- Added / Removed
  DetectedAt DATETIME2(0) NOT NULL
);

CREATE TABLE dbo.SqlDbUser (
  SqlDbUserId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  SqlInstanceId INT NOT NULL REFERENCES dbo.SqlInstance(SqlInstanceId),
  DatabaseName NVARCHAR(128) NOT NULL,
  UserName NVARCHAR(256) NOT NULL,
  UserType NVARCHAR(60) NULL,
  IsOrphaned BIT NOT NULL CONSTRAINT DF_SqlDbUser_Orphan DEFAULT (0),
  IsDbOwner BIT NOT NULL CONSTRAINT DF_SqlDbUser_DbOwner DEFAULT (0),
  LastSeenAt DATETIME2(0) NOT NULL,
  CONSTRAINT UQ_SqlDbUser UNIQUE (SqlInstanceId, DatabaseName, UserName)
);

CREATE TABLE dbo.SqlSecurityFinding (
  FindingId BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  SqlInstanceId INT NOT NULL REFERENCES dbo.SqlInstance(SqlInstanceId),
  FindingType VARCHAR(64) NOT NULL,      -- UnexpectedSysadmin / OrphanedUser / XpCmdshellOn / GuestEnabled / TrustworthyOn
  SeverityCode VARCHAR(16) NOT NULL,     -- Critical/High/Medium (align AlertSeverity if shared)
  EntityKey NVARCHAR(256) NOT NULL,
  Title NVARCHAR(256) NOT NULL,
  Detail NVARCHAR(1000) NULL,
  FirstSeenAt DATETIME2(0) NOT NULL,
  LastSeenAt DATETIME2(0) NOT NULL,
  IsOpen BIT NOT NULL CONSTRAINT DF_SecFinding_Open DEFAULT (1),
  CONSTRAINT UQ_SqlSecurityFinding_Open UNIQUE (SqlInstanceId, FindingType, EntityKey)
);

CREATE TABLE dbo.SqlSecurityException (
  ExceptionId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  SqlInstanceId INT NULL,                 -- NULL = estate-wide
  FindingType VARCHAR(64) NOT NULL,
  EntityKey NVARCHAR(256) NULL,
  Reason NVARCHAR(512) NOT NULL,
  ApprovedBy NVARCHAR(128) NOT NULL,
  ApprovedAt DATETIME2(0) NOT NULL,
  ExpiresAt DATETIME2(0) NULL
);

Finding rules (MVP):

Type	Severity	When
UnexpectedSysadmin	Critical	In sysadmin now; not present in prior snapshot (first run: skip or Medium “Inventory”)
OrphanedUser	High	DB user sid not mapped (user DBs)
XpCmdshellOn	Critical	`sp_configure` xp_cmdshell run_value = 1
GuestEnabled	High	guest has CONNECT in user DB
TrustworthyOn	Medium	user DB `is_trustworthy_on = 1`

Procs:

CREATE PROC dbo.usp_SqlSecurity_ApplyLoginSnapshot @SqlInstanceId INT /* + TVP logins */ AS ...
-- upsert SqlLogin; compute sysadmin Added/Removed into history; open/close UnexpectedSysadmin findings

CREATE PROC dbo.usp_SqlSecurity_ApplyDbUserSnapshot @SqlInstanceId INT /* + TVP */ AS ...
CREATE PROC dbo.usp_SqlSecurity_ApplyConfigFlags @SqlInstanceId INT, @XpCmdshellOn BIT, /* per-db flags TVP */ AS ...

CREATE PROC dbo.usp_SqlSecurity_GetExceptionsBoard
  @OpenOnly BIT = 1 AS
BEGIN
  SELECT f.*, i.HostName, i.InstanceName
  FROM dbo.SqlSecurityFinding f
  JOIN dbo.SqlInstance i ON i.SqlInstanceId = f.SqlInstanceId
  WHERE (@OpenOnly = 0 OR f.IsOpen = 1)
    AND NOT EXISTS (
      SELECT 1 FROM dbo.SqlSecurityException x
      WHERE x.FindingType = f.FindingType
        AND (x.SqlInstanceId IS NULL OR x.SqlInstanceId = f.SqlInstanceId)
        AND (x.EntityKey IS NULL OR x.EntityKey = f.EntityKey)
        AND (x.ExpiresAt IS NULL OR x.ExpiresAt > SYSUTCDATETIME())
    )
  ORDER BY CASE f.SeverityCode WHEN 'Critical' THEN 0 WHEN 'High' THEN 1 ELSE 2 END, f.FirstSeenAt;
END;

CREATE PROC dbo.usp_SqlSecurity_GetSysadminRoster @SqlInstanceId INT = NULL AS ...
CREATE PROC dbo.usp_SqlSecurity_GetSysadminDiff @Days INT = 30 AS ...

Evaluate closes findings when condition clears (IsOpen = 0).

B. Collect SQL / PowerShell (shape)

Logins / sysadmin:

SELECT p.name, p.type_desc, p.is_disabled, p.create_date, p.modify_date,
       l.is_policy_checked, l.is_expiration_checked,
       IS_SRVROLEMEMBER('sysadmin', p.name) AS IsSysadmin
FROM sys.server_principals p
LEFT JOIN sys.sql_logins l ON l.principal_id = p.principal_id
WHERE p.type IN ('S','U','G') AND p.name NOT LIKE '##%';

Orphans (per user DB): users with sid not in server principals / sp_change_users_login report style — prefer:

-- run in each user DB
SELECT DB_NAME() AS DatabaseName, dp.name AS UserName, dp.type_desc,
       CASE WHEN sp.sid IS NULL AND dp.authentication_type_desc <> 'DATABASE' THEN 1 ELSE 0 END AS IsOrphaned,
       IS_MEMBER('db_owner') -- careful: use role membership query instead for named user

Use a proper role check for db_owner membership per user in collector.

Config: xp_cmdshell via sys.configurations; trustworthy/guest via DB loop.

Script: Collect-SqlSecurityHygiene.ps1
Phase 2: AD expand for WINDOWS_GROUP into AdGroupMember (table later).

Rights: VIEW ANY DEFINITION / VIEW SERVER STATE / CONNECT any DB as needed — prefer custom role over sysadmin for collector.

C. Blazor Server (MVP UI)

Page: /security or /sql/security

Panels:

Exceptions board — Severity | Type | Host\Instance | Entity | Title | First/Last seen
Sysadmin roster — current IsSysadmin = 1
Recent sysadmin diff — Added/Removed (30d)

Filters: Critical only; finding type; instance

Actions v1: link SQL-05 runbook; Request exception can be Notes-only / out of band — or simple form writing SqlSecurityException if Rick wants (optional). No revoke.

App pool: read procs (+ optional exception insert with audit user)

D. Acceptance checks

[ ] Snapshot loads logins; sysadmin members listed
[ ] Add lab login to sysadmin → next collect creates UnexpectedSysadmin + history Added
[ ] Remove → finding closes / Removed history
[ ] Orphaned user in lab DB → OrphanedUser finding
[ ] Enable xp_cmdshell in lab → Critical finding; disable → closes
[ ] Approved exception hides finding from board while expiry valid
[ ] UI never shows hashes/passwords

E. Repo layout

/sql/security/001_tables.sql
/sql/security/002_procs.sql
/ps/Security/Collect-SqlSecurityHygiene.ps1
/src/.../Pages/Security/Index.razor
/src/.../Services/SecurityHygieneService.cs

Hook for spike #7 (later)

Map open Critical/High findings → SecurityFinding alert type with EntityKey.

Next (wait for pick)

Optional: #8 Build out-of-date list.

SQL Dude — SQL-SPIKE-06 Security Exceptions Board v1