Dependency & risk report: SQLHOST01

01

Executive summary

SQLHOST01 hosts one live database, OrphanTestDB, with six objects and two SQL Agent jobs. Around it sit six loose files, a batch script, a PowerShell script, a T-SQL script, an Excel workbook, an SSIS package, and an SSRS report, each of which reaches into the database through a hardcoded connection string. Nothing on this box is documented; the map below was reconstructed entirely from what the server and the files disclose.

One thing is already broken. The nightly job OrphanTest_NightlyLoad is enabled and runs every night, but its second step fails every time, and has on both of its two most recent runs. The first step succeeds, so the job looks like it is working to anyone glancing at a job list. It is not: the half that pulls legacy data never completes.

The failing step depends on a server outside this box, ARCHIVE-SQL, through a reference that a normal catalog inventory cannot see, because it is assembled as dynamic SQL at runtime. A second, independent path to that same external server exists inside the SSIS package. Both are single points of failure that no one currently owns: the jobs were last invoked by SQLHOST01\olddev, a login that appears nowhere else.

02

Inventory snapshot

03

The map

The full graph holds 23 nodes and 41 edges. Below is the part that carries the risk: the two jobs, what they run, and the two independent paths that leave this box for an external server. Amber marks what is failing or unconfirmed; green marks what runs and works.

04

Risk register

Ranked highest severity first. Severity is computed from signals in the graph, not written by hand, so it is repeatable. Every finding carries its evidence.

05

Dead vs live

What is actually running today versus what is referenced but not confirmed reachable. A read-only inventory of one box can prove a dependency exists; it cannot cross a linked server to prove the far end is alive, so external targets are marked accordingly rather than guessed.

06

Dependency narratives

The critical chains in plain English. Each sentence maps to a cited edge above.

07

Coverage & confidence

What the collector saw directly, and what it could not, so you know which findings to lean on before you act. These caveats are stamped from the tool's own coverage table, not softened.

08

Appendix: raw cited edges

For the skeptic. A sample of the graph edges behind the findings above, exactly as the tool emitted them, so any dependency can be verified by hand.

// the linked-server reference the catalog misses
{ "src": "obj:OrphanTestDB.dbo.usp_PullLegacy",
  "dst": "lnk:ARCHIVE-SQL",
  "type": "REFERENCES_LINKED_SERVER",
  "source": { "type": "module_line", "line": 6,
             "text": "...SELECT * FROM [ARCHIVE-SQL].[LegacyDB].[dbo].[ExternalCustomers]..." } }

// the CmdExec step that invokes a batch file on a share
{ "src": "step:OrphanTest_FileLoad#1",
  "dst": "file:\\FILESERVER\\Legacy\\legacyshare\\nightly_load.bat",
  "type": "INVOKES",
  "source": { "type": "jobstep", "object": "msdb.dbo.sysjobsteps",
             "key": "job_id=7601F29E..., step_id=1" } }

// SSIS package connection string, second path to the external server
{ "src": "file:...\\loadcustomers.dtsx",
  "dst": "db:LegacyDB",
  "type": "CONNECTS_TO",
  "source": { "type": "file_xml", "line": 9,
             "text": "Data Source=ARCHIVE-SQL;Initial Catalog=LegacyDB;..." } }