Security & Access Hygiene

Status: Reviewed (deep-dive v1)
Stack: PowerShell (AD group membership) → SQL permission snapshots/procs → Blazor Server UI
Depends on: SQL-01 Inventory (instance population)
Goal: See who has powerful access, catch drift (new sysadmins, orphans, risky surface), and keep fixes change-controlled.

Why this closes the first five

You can inventory, back up, maintain indexes, and watch Agent — and still get burned by sprawling sysadmin, orphaned users, or guest/xp_cmdshell leftovers. Hygiene is continuous, not a one-time audit.

What “good” looks like

Server principals + DB users inventoried on a cadence
sysadmin / Control Server / high DB roles are listed and justified
Orphaned users (no matching login) flagged
Risky config surfaced: xp_cmdshell, guest enabled in user DBs, unpatched/old TLS notes (high level), TRUSTWORTHY on user DBs
Windows login → AD group expansion available for review
Blazor shows exceptions, not every permission row
No passwords, secrets, or connection strings in UI or Notes

Priority findings (RAG starting rules — confirm with Rick)

Finding	Severity	Notes
New/unexpected `sysadmin` (Windows or SQL)	Red	Diff vs last snapshot
Orphaned users in user DBs	Yellow/Red	Higher if dbo/owner
`xp_cmdshell` enabled	Red	Unless documented exception
Guest enabled in user DB	Yellow/Red	Especially prod
SQL logins with blank/weak policy flags	Red	`is_policy_checked` / expired
Excessive `db_owner` on prod app logins	Yellow	Prefer least privilege
TRUSTWORTHY ON (user DB)	Yellow	Review ownership chaining risk
Public/guest granted extras	Yellow	Unusual grants

Checklist — collect (PowerShell + T-SQL)

Scope = inventoried instances.

T-SQL (read-only security DMVs/catalog):

[ ] Server logins / principals: name, type, disabled, create/modify, policy, expiration, sysadmin and other server roles
[ ] Endpoint / permissions high-water: Control Server, Alter Any Login, etc. (surface only high privilege)
[ ] Per DB: users, roles (db_owner, db_securityadmin, …), orphaned users (sid mismatch)
[ ] Config: xp_cmdshell, show advanced options not required in report — just the risk flags
[ ] DB properties: is_trustworthy_on, membership of guest

PowerShell / AD:

[ ] For Windows logins/groups: expand AD group membership (nested, carefully) into warehouse
[ ] Optional: flag accounts disabled in AD but still enabled as login

Emit CollectedAt + instance key. Never collect password hashes for display.

Checklist — store (SQL + stored procedures)

Table	Purpose
`dbo.SqlLogin`	Server principals snapshot (latest)
`dbo.SqlLoginHistory`	Prior snapshots for sysadmin drift
`dbo.SqlDbUser`	DB users + roles summary
`dbo.SqlSecurityFinding`	Computed findings (type, severity, object, first/last seen)
`dbo.SqlSecurityException`	Approved exceptions (who/when/why/expiry)
`dbo.AdGroupMember`	Expanded Windows group members

Procs:

[ ] usp_SqlSecurity_ApplySnapshot — upsert principals/users; generate findings; clear fixed ones
[ ] usp_SqlSecurity_GetExceptionsBoard — Blazor main grid (open findings minus approved)
[ ] usp_SqlSecurity_GetLoginDetail — roles + AD expansion
[ ] usp_SqlSecurity_GetSysadminDiff — added/removed since last collect

Checklist — present (Blazor Server)

[ ] Exceptions board: open findings by severity
[ ] Sysadmin roster with last-seen / newly added badge
[ ] Orphaned users list (instance, db, user)
[ ] Filters: Red only, finding type, instance
[ ] Detail: AD members for a Windows group login (paged)
[ ] Actions v1: link runbook + mark “exception requested” — no revoke from UI

Operator runbook (short)

Open Exceptions board → Red first
Unexpected sysadmin: confirm change ticket; if not, disable login / remove role under change control; rotate any shared creds
Orphaned user: remapped (sp_change_users_login / ALTER USER … WITH LOGIN) or drop if unused
xp_cmdshell: disable unless documented; find what enabled it (Agent job, deploy script)
Guest / TRUSTWORTHY: fix with app owner approval
After fix: next collect should clear finding; if not, verify you fixed the right instance
Record lasting exceptions in SqlSecurityException with expiry

Least-privilege patterns (for Rick’s stack)

Layer	Prefer
PowerShell collectors	Dedicated account; local admin only if required for service discovery; SQL login with least read + write to inventory DB only
SQL upsert procs	Writer login ≠ Blazor login
Blazor app pool	`EXECUTE` on read procs only; no `sysadmin`; no Agent start/stop
Humans	Windows groups → server roles; avoid personal SQL logins in prod

Common failure patterns

Symptom	Likely cause
Sysadmin churn every collect	Deploy scripts adding service accounts
Orphans after restore/migrate	SID mismatch across domains/instances
Findings flap open/closed	Exception not recorded; intermittent sync
AD expand empty	Group scope / trust / collector rights

Permissions notes

Security collect often needs VIEW ANY DEFINITION / VIEW SERVER STATE / ability to read role membership — prefer custom role over sysadmin for the collector
AD expand: directory read rights
Blazor: read procs only
Remediations: DBA + change control only

Done definition for this topic

[ ] Snapshots for inventoried instances
[ ] Exceptions board shows real findings (or clean + empty)
[ ] Sysadmin diff works across two collects
[ ] This note linked from the Knowledge Base list item

First-five wrap

#	Topic	Doc
01	Inventory & configuration baseline	`SQL-01-Inventory-Baseline.md`
02	Backup & restore readiness	`SQL-02-Backup-Restore-Readiness.md`
03	Index & statistics maintenance	`SQL-03-Index-Statistics-Maintenance.md`
04	SQL Agent jobs & schedules	`SQL-04-SQL-Agent-Jobs-Schedules.md`
05	Security & access hygiene	`SQL-05-Security-Access-Hygiene.md`

Beyond the seed (when Rick directs): capacity/growth, performance health, patching, HA/DR, alerting/runbooks (see outline).

SQL Dude — SQL-05 Security & Access Hygiene v1