System stored procedures (practical)

Status: Reviewed
Stack: PowerShell → SQL / stored procs → Blazor
Depends on: SQL-CLI-01 (sqlcmd/Invoke-Sqlcmd to call them)
Goal: Know which built-in procs to call from CLI/scripts for inventory, config, security, Agent, and maintenance — and which to avoid or replace with DMVs.

Sources: Microsoft Learn (sql-server-ver17 / 2026 docs). Curated for Rick’s ops loop — not an encyclopedia of every sp_* / xp_*.

1. How to call from Rick’s stack

Pointer only — flags, auth, and exit codes live in SQL-CLI-01. Patterns for staging → Blazor live in SQL-CLI-07.

Parameters

• Prefer named args: EXEC dbo.sp_helpdb @dbname = N'AdventureWorks';
• System procs live in master (or msdb for Agent). Qualify when the session DB is a user DB: EXEC master.dbo.sp_configure; / EXEC msdb.dbo.sp_help_job;
• Many sp_help* return multiple result sets — sqlcmd prints them all; Invoke-Sqlcmd returns the last set unless you use lower-level ADO.NET / SqlCommand. For Blazor inventory, prefer catalog views / DMVs (single, queryable result set) over multi-set help procs.

Permissions (high level)

2. Decision: system procs vs DMVs / catalog views

Rule for this stack: collect with DMVs/catalog in .sql files; reserve system procs for (a) config changes, (b) Agent control, (c) rare interactive triage.

3. Curated catalog (high-value only)

Columns: Proc · Purpose · Typical args · Perm note · Prefer instead?

3.1 Instance / config

sp_configure caveats (ops)

1. Show advanced: EXEC sp_configure 'show advanced options', 1; RECONFIGURE; — then set option — then ideally flip advanced back to 0.
2. After set, config_value ≠ run_value until RECONFIGURE. Confirm with sys.configurations.
3. is_dynamic = 0 → restart required even after successful RECONFIGURE.
4. Surface-area options (xp_cmdshell, Ole Automation Procedures, clr enabled, etc.) are policy decisions, not casual toggles.
5. Database-scoped options → ALTER DATABASE SCOPED CONFIGURATION, not sp_configure.

3.2 Database / objects

3.3 Security / principals

Modern alternatives (bookmark)

3.4 SQL Server Agent (msdb)

Call as EXEC msdb.dbo.sp_start_job @job_name = N'Nightly Inventory';

3.5 Replication / Database Mail / niche

Skip deep coverage unless you operate them daily.

See niche: if Rick hits a specific subsystem, spin a one-pager — don’t bloat this sheet.

3.6 Extended procedures xp_* — security warning

Heavy warning: any xp_* that touches the OS runs in the engine’s trust boundary. CIS / auditors flag xp_cmdshell = 1. For Rick’s AD → PS → SQL → Blazor path, OS work stays in PowerShell; SQL stores and transforms data.

4. Deprecated / avoid (short)

Still OK when you know why: sp_configure, Agent sp_*_job*, sp_spaceused (with @oneresultset), sp_rename for tables/columns/indexes, sp_validatelogins, interactive sp_help*.

5. Copy-paste recipes (inventory → Blazor staging)

Assume Windows auth (-E). Swap to CLI-06 patterns for Entra/SQL auth. Always -b / check exit codes (CLI-01, CLI-07).

5.1 Config snapshot via catalog (preferred) + optional proc cross-check

$sql = @"
SET NOCOUNT ON;
SELECT
  name,
  value AS config_value,
  value_in_use AS run_value,
  is_dynamic,
  is_advanced
FROM sys.configurations
ORDER BY name;
"@
Invoke-Sqlcmd -ServerInstance $srv -Database master -Query $sql |
  Export-Csv ".\staging\config_$($srv -replace '\\','_').csv" -NoTypeInformation

# Interactive / one-off only
sqlcmd -S $srv -E -d master -b -Q "EXEC sp_configure;"

5.2 Database inventory (catalog, not sp_helpdb)

$sql = @"
SET NOCOUNT ON;
SELECT
  d.name, d.database_id, d.state_desc, d.recovery_model_desc,
  d.compatibility_level, d.is_read_only, d.is_encrypted,
  SUSER_SNAME(d.owner_sid) AS owner_name,
  d.create_date
FROM sys.databases AS d
ORDER BY d.name;
"@
Invoke-Sqlcmd -ServerInstance $srv -Database master -Query $sql |
  Export-Csv ".\staging\databases_$env:COMPUTERNAME.csv" -NoTypeInformation

5.3 Space used — proc with one result set (acceptable) vs DMV (preferred)

# Acceptable: single result set for PS
sqlcmd -S $srv -E -d AdventureWorks -b -Q "EXEC sp_spaceused @oneresultset = 1;" -s"," -W -o ".\staging\space_aw.csv"

-- Preferred fleet query (run per DB or via cursor/PS loop)
SET NOCOUNT ON;
SELECT
  DB_NAME() AS database_name,
  OBJECT_SCHEMA_NAME(p.object_id) AS [schema_name],
  OBJECT_NAME(p.object_id) AS table_name,
  SUM(p.rows) AS row_count,
  SUM(a.total_pages) * 8 AS total_kb,
  SUM(a.used_pages) * 8 AS used_kb
FROM sys.partitions AS p
JOIN sys.allocation_units AS a ON p.partition_id = a.container_id
WHERE p.index_id IN (0, 1)
GROUP BY p.object_id
ORDER BY total_kb DESC;

5.4 Session / blocking snapshot (DMV — feed Blazor)

$sql = @"
SET NOCOUNT ON;
SELECT
  s.session_id, s.login_name, s.host_name, s.program_name,
  s.status AS session_status, r.status AS request_status,
  r.blocking_session_id, r.wait_type, r.wait_time,
  r.cpu_time, r.total_elapsed_time,
  DB_NAME(r.database_id) AS database_name,
  SUBSTRING(t.text, 1, 400) AS statement_text
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE s.is_user_process = 1
ORDER BY r.total_elapsed_time DESC;
"@
Invoke-Sqlcmd -ServerInstance $srv -Database master -Query $sql |
  Export-Csv ".\staging\sessions.csv" -NoTypeInformation

5.5 Orphaned users report (modern — no sp_change_users_login)

-- Run in each user database
SET NOCOUNT ON;
SELECT
  dp.name AS db_user,
  dp.type_desc,
  dp.sid
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON dp.sid = sp.sid
WHERE dp.type IN ('S', 'U', 'G')
  AND dp.sid IS NOT NULL
  AND sp.sid IS NULL
  AND dp.name NOT IN (N'guest', N'INFORMATION_SCHEMA', N'sys');
-- Fix: ALTER USER [db_user] WITH LOGIN = [login_name];

5.6 Agent job inventory + start (control vs dashboard)

# Inventory → staging
$sql = @"
SET NOCOUNT ON;
SELECT
  j.name, j.enabled, j.date_created, j.date_modified,
  SUSER_SNAME(j.owner_sid) AS owner_name,
  c.name AS category
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.dbo.syscategories AS c ON j.category_id = c.category_id
ORDER BY j.name;
"@
Invoke-Sqlcmd -ServerInstance $srv -Database msdb -Query $sql |
  Export-Csv ".\staging\agent_jobs.csv" -NoTypeInformation

# Control plane — start a known job
sqlcmd -S $srv -E -d msdb -b -Q "EXEC msdb.dbo.sp_start_job @job_name = N'Nightly Inventory';"
if ($LASTEXITCODE -ne 0) { throw "sp_start_job failed: $LASTEXITCODE" }

5.7 Module definition pull (catalog, not sp_helptext)

$sql = @"
SET NOCOUNT ON;
SELECT
  OBJECT_SCHEMA_NAME(m.object_id) AS schema_name,
  o.name AS object_name,
  o.type_desc,
  m.definition
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON m.object_id = o.object_id
WHERE o.type IN ('P', 'V', 'FN', 'IF', 'TF', 'TR')
ORDER BY schema_name, object_name;
"@
Invoke-Sqlcmd -ServerInstance $srv -Database $db -Query $sql |
  Export-Csv ".\staging\modules_$db.csv" -NoTypeInformation

5.8 Validate Windows logins after AD changes

sqlcmd -S $srv -E -d master -b -Q "EXEC sp_validatelogins;" -s"," -W -o ".\staging\invalid_windows_logins.csv"

6. Permissions cheat

Blazor note (CLI-07): ops inventory identity ≠ app-pool identity. Grant the collector VIEW SERVER STATE (or 2022 performance state) + read on staging; grant Blazor EXEC on page procs / SELECT on views only.

Quick ops checklist

• [ ] New inventory script → catalog/DMV first, help-proc only if stuck
• [ ] Config change → sp_configure → RECONFIGURE → verify value vs value_in_use → restart if is_dynamic = 0
• [ ] Orphans → SID compare / ALTER USER, not sp_change_users_login
• [ ] Agent dashboards → sysjobs* tables; Agent control → sp_start_job / sp_stop_job
• [ ] xp_cmdshell → leave off; do OS work in PowerShell
• [ ] Multi-result sp_help* → don’t pipe into Blazor staging without shaping

Open questions / follow-ups

Documented for Rick in the meta companion — decide numbering (CLI-09 vs Dude), WhoIsActive as optional cite, and Azure SQL gaps (no Agent / limited xp_*).