Synonyms & Cross-DB Access

Status: Reviewed (deep-dive v1)
Stack note: PowerShell collectors hit instance DBs; boards and merges live in the warehouse. Keep that boundary clean — synonyms (and disciplined three-part names) so usp_* don’t hardcode server.database soup in every proc. Blazor talks only to warehouse procs (SQL-12).
Pairs with: SQL-01/SPIKE-01 (inventory), SQL-19 (merges), SQL-05 (cross-db rights), SQL-09 (linked/AG naming).
Goal: When to use synonyms, how to shape warehouse↔instance access for PS, and what not to do.

Quick chooser

Need	Prefer	Avoid
Stable name for an object that moves/renames	Synonym in warehouse	Find/replace three-part names everywhere
PS collect from many instances	Connect per instance → stage → warehouse merge	One linked-server cursor mega-proc as default
Board reads	Warehouse tables only	Blazor querying instance DBs directly
Rare admin reach into instance	Controlled linked server / PS remoting	App pool with rights on every prod DB
Cross-db same instance	Three-part `db.schema.object` or synonym	Dynamic SQL with raw DB names from UI (SQL-14)

Mental model (this estate)

[SQL instances] --PS collectors--> staging / TVP --> [Warehouse] --usp_*--> [Blazor]
                      |                                ^
                      +----- optional synonyms --------+  (local aliases only)

Collectors: credential + connection string to the instance; write results into warehouse staging
Warehouse procs: own the merge/board logic; don’t impersonate “connect to 200 servers” inside one Blazor call
Synonyms: local nicknames for objects (same server different DB, or linked target) — not a replacement for inventory

Synonym basics

-- In warehouse: alias a table in another DB on same instance
CREATE SYNONYM dbo.LegacyConfig FOR ConfigDB.dbo.AppSetting;

-- Alias via linked server (use sparingly)
CREATE SYNONYM dbo.RemoteProbe FOR LinkedSql01.msdb.dbo.sysjobs;

-- Use like a local object
SELECT * FROM dbo.LegacyConfig;

Notes:

Synonyms are thin — permissions still needed on the base object (or ownership chaining / cert signing carefully)
DROP/CREATE to retarget after rename
Great for migration (old name → new) and multi-env (synonym target differs by environment script)

-- env deploy pattern: script sets target
CREATE SYNONYM dbo.RefCalendar FOR $(RefDb).dbo.Calendar;

Cross-DB same SQL instance

Pattern	Use
`Warehouse.dbo.usp_…` reading `InventoryAux.dbo.T`	Three-part name or synonym
Same-instance reporting	Synonym keeps proc text stable if aux DB renamed
Permissions	Grant on base objects; test as app/Agent account

Don’t grant Blazor db_owner on aux DBs “so joins work.”

Cross-instance (linked servers)

Prefer PS multi-connect for estate collect:

Inventory list from warehouse (SqlInstance)
PS foreach instance (throttle, timeout, error log)
Bulk load stage → usp_*_Merge (SQL-19)

Linked servers when:

Small fixed set of remotes
Server-side job must pull without PS
You accept ops cost (login mapping, RPC, firewall, plan weirdness)

Checklist if you use them:

[ ] Least-privilege remote login mapping
[ ] RPC/RPC OUT only if required
[ ] Synonym wraps four-part name so procs don’t repeat it
[ ] Timeouts + Provider options documented
[ ] Failures surface to Agent/SQL-10 — don’t swallow

What Blazor / app pool must not do

[ ] Open connections to random instance hosts from the web tier
[ ] Build four-part names from UI text
[ ] Use synonyms as a back door to mutate prod instance config
[ ] Hold transactions open across linked-server round trips (SQL-18)

Boards = warehouse. Remediation = ops/PS/Agent with separate rights (SPIKE hard rule).

Naming & repo habits

/sql/warehouse/synonyms/001_ref_synonyms.sql
/sql/warehouse/procs/...
/ps/Collect-SqlInventory.ps1   # connects to instances, writes warehouse

Synonym create scripts per environment (dev/test/prod targets)
Document: synonym name → real target
Inventory of linked servers = ops artifact (who approved, what login)

Permissions sketch

Principal	Warehouse	Instance DBs
Blazor app pool	`EXECUTE` on `usp_*` (+ TVP types)	None
Agent merge job	Write staging + merge procs	None (data already staged)
PS collector	Write staging / exec merge	Read dmvs / needed views per instance (least priv)
DBA	Broader	Broader

Cross-db synonym read: grant SELECT on base table to the executing principal (or use module signing — advanced; document if used).

Common failure patterns

Symptom	Likely cause
Synonym works for you, fails for app	Missing grants on base object
Slow board suddenly	Proc started using linked server path
Collector “works in IDE”	Different login mapping
Broken after restore/rename	Synonym target stale
Security finding	App pool reaching instances

Tiny lab

Two DBs on one instance — synonym + proc in warehouse selecting through it
Deny app login on base table — confirm failure; grant least priv
Time PS-per-instance collect vs linked pull for N hosts — pick default

Shortlist wrap (18→23)

#	Topic
18	Transactions & isolation
19	MERGE & idempotent upserts
20	Query Store for procs
21	TVPs from Blazor/PS
22	Temporal / history
23	Synonyms & cross-DB (this note)

Done when

[ ] Blazor → warehouse only; collectors own instance connections
[ ] Synonyms used for stable aliases / env retarget — not as security boundary alone
[ ] Linked servers documented if any; PS preferred for estate-wide collect
[ ] This note linked from Knowledge Base list

SQL Dude — SQL-23 Synonyms & Cross-DB Access v1