Status: Draft (companion to SQL-CLI-09)
Stack note: Inventory and hygiene scripts (PowerShell → SQL) need a short list of principal/permission helpers — prefer DMVs for ongoing boards; use these procs for ad-hoc CLI checks.
Pairs with: System stored procedures, Security hygiene.
Goal: Know the common security/principal system procs and when a DMV is better.
| Need | Prefer | Notes |
|---|---|---|
| Who can log in / orphaned users | sp_helplogins, sp_change_users_login (legacy) |
Prefer modern ALTER USER ... WITH LOGIN |
| Database users / roles | sp_helpuser, sp_helprolemember |
Catalog views often clearer for scripts |
| Permissions dump (legacy) | sp_helprotect |
Prefer sys.database_permissions for Blazor boards |
| Server perms | DMVs (sys.server_permissions) |
Usually better than procs for collection |
-- Logins vs users (high level)
EXEC sp_helplogins;
-- Users in current DB
EXEC sp_helpuser;
-- Role members
EXEC sp_helprolemember @rolename = N'db_owner';
From PowerShell: Invoke-Sqlcmd -Query "EXEC sp_helplogins" → objects for staging; don’t scrape text grids into Blazor if you can query catalog views instead.
SELECT p.name AS principal_name, p.type_desc, perm.permission_name, perm.state_desc
FROM sys.database_permissions perm
JOIN sys.database_principals p ON p.principal_id = perm.grantee_principal_id;
Use system procs for interactive sqlcmd triage; use catalog/DMVs for SPIKE security boards (see SQL-05 / SPIKE-06).
EXEC.xp_* / open-ended security procs broadly.sp_helprotect text.sp_change_users_login usage as legacy migration only.