# Sqlmigration Security Review

> Audits the security-object migration plan for a SQL Server move with 15 checks (J1-J15) — logins, server and database permissions, credentials, certificates/keys ownership, and Central Management Server registrations. Use this skill when a user is migrating a SQL Server database or instance and needs to carry over logins, permissions, credentials, or certificates, or asks "will my logins still work after migration" or "how do I migrate permissions." Trigger whenever security-object portability during a migration is the topic; dispatched here from /sqlmigration-review for the security-object family.

- Skill: `vanterx/sqlmigration-security-review` (Agent Skill, multi-file: 7 files)
- Install (CLI): `npx skillmds@latest add vanterx/sqlmigration-security-review`
- Raw SKILL.md: https://api.skillmd.com/api/skills/vanterx/sqlmigration-security-review/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: vanterx (https://skillmd.com/u/vanterx)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/vanterx/sqlmigration-security-review

---


# sqlmigration-security-review

## Purpose

Reviews the security-object family of a SQL Server migration — the slice `sqlmigration-review`
dispatches here rather than checking itself. This skill owns 15 checks (J1–J15) covering whether
logins, server/database permissions, credentials, certificate/key ownership, and Central
Management Server (CMS) registrations will survive a backup/restore or log shipping/Always On
AG seeding migration, and what breaks if they don't.

- **Login Portability (J1–J5)** — orphaned users after restore, SID mismatch, login type
  unsupported on target platform, password policy differences, default database missing
- **Permission Fidelity (J6–J9)** — server-level role membership, database-level role membership,
  explicit GRANT/DENY statements, ownership chains crossing the migration boundary
- **Credentials & Secrets (J10–J12)** — SQL Server Credential objects, proxy account mapping,
  linked server stored logins
- **Certificates & Keys (J13–J14)** — certificate/key migration for objects that depend on them
  (excluding TDE, which is `sqlencryption-review`'s domain), backup of certificates before cutover
- **CMS (J15)** — Central Management Server registration entries pointing at the old instance name

All fix recipes use native T-SQL system views (`sys.server_principals`, `sys.database_principals`,
`sys.credentials`), the in-box `SqlServer` PowerShell module, and the native `sp_help_revlogin`
script — no third-party module is referenced or required.

## Input

Accepts any of the following:

1. **Source and target server facts pasted as text** — login lists, permission grants, target
   platform
2. **Capture script output** (`.txt`/`.csv`) — see `scripts/capture-security-facts.sql`
3. **Natural-language description** — "migrating 40 logins, half are Windows groups, target is
   Azure SQL Database"
4. **File path to a directory of exported artifacts** — `sp_helplogins` text dumps, SSMS
   Generate Scripts output for logins/users

Recommended capture (run on the **source** instance):

```sql
SELECT name, type_desc, is_disabled, default_database_name,
       SUSER_SID(name) AS sid
FROM sys.server_principals
WHERE type IN ('S','U','G') AND name NOT LIKE '##%';

SELECT dp.name AS user_name, dp.type_desc, sp.name AS login_name
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S','U','G');

SELECT name, credential_identity FROM sys.credentials;

-- TDE encryptor certs also have is_active_for_begin_dialog = 0, so anti-join the DEK view to drop them
SELECT c.name, c.certificate_id, c.expiry_date, c.pvt_key_encryption_type_desc
FROM sys.certificates c
WHERE NOT EXISTS (SELECT 1 FROM sys.dm_database_encryption_keys dek
                  WHERE dek.encryptor_thumbprint = c.thumbprint);
```

## Category 1 — Login Portability

### J1 — Orphaned Database User After Restore
**Trigger:** A database user's SID (`sys.database_principals.sid`) has no matching login SID
(`sys.server_principals.sid`) on the target instance after restore.
**Severity:** Critical
**Fix:** Run `ALTER USER [username] WITH LOGIN = [username];` for each orphaned user once the
matching login exists on the target, or use `sp_help_revlogin`'s generated script to recreate
logins with matching SIDs before the restore completes.

### J2 — SQL Authentication Login SID Mismatch Risk
**Trigger:** Migration plan creates new SQL-authentication logins on the target manually (e.g.,
via `CREATE LOGIN`) rather than scripting them from the source with matching SIDs.
**Severity:** Critical
**Fix:** Generate the login-creation script from the source using `sp_help_revlogin` (in-box
system stored procedure, or its documented script form), which preserves the original SID and
hashed password — manually re-typing `CREATE LOGIN` statements produces a new SID and orphans
every database user mapped to that login.

### J3 — Login Type Unsupported on Target Platform
**Trigger:** Source has Windows-authenticated logins/groups, certificate-mapped logins, or
asymmetric-key-mapped logins, and the target platform is Azure SQL Database (no Windows Auth
support) or otherwise cannot host that login type.
**Severity:** Critical
**Fix:** Map Windows logins to Microsoft Entra ID identities for Azure SQL Database targets;
confirm certificate/key-mapped login support on the specific target platform before migrating —
see `/sqlmigration-review` Y8 for the broader Azure platform check.

### J4 — Password Policy / Expiration Settings Differ From Source
**Trigger:** Source logins have `CHECK_POLICY`/`CHECK_EXPIRATION` settings that depend on a
domain password policy not enforced identically on the target (e.g., target is a workgroup
server or Azure SQL Database with no domain policy).
**Severity:** Warning
**Fix:** Re-evaluate each migrated login's `CHECK_POLICY`/`CHECK_EXPIRATION` settings explicitly
on the target rather than assuming the source's domain policy carries over silently.

### J5 — Migrated Login's Default Database Does Not Exist on Target
**Trigger:** A login's `default_database_name` references a database that either was not part of
the migration scope or has a different name on the target.
**Severity:** Warning
**Fix:** `ALTER LOGIN [loginname] WITH DEFAULT_DATABASE = [target_db_name];` for each affected
login after the database has been migrated and renamed (if applicable).

## Category 2 — Permission Fidelity

### J6 — Server-Level Role Membership Not Re-Created
**Trigger:** Source login is a member of a fixed or user-defined server role
(`sys.server_role_members`), and the migration plan's login-recreation script does not include
role membership statements.
**Severity:** Critical
**Fix:** Script `ALTER SERVER ROLE [rolename] ADD MEMBER [loginname];` for every server-role
membership captured on the source, and run it immediately after login creation on the target.

### J7 — Database Role Membership Lost on User Recreation
**Trigger:** A database user is a member of a database role (`sys.database_role_members`), and
the orphaned-user fix (J1) recreates the user without reassigning role membership.
**Severity:** Warning
**Fix:** Capture `sys.database_role_members` before migrating and re-run
`ALTER ROLE [rolename] ADD MEMBER [username];` for each membership after fixing orphaned users.

### J8 — Explicit Object-Level GRANT/DENY Not Captured in Migration Scripts
**Trigger:** `sys.database_permissions` shows explicit (non-role-based) GRANT or DENY statements
on objects within the migrated database, and the migration plan only scripts logins/users, not
explicit permissions.
**Severity:** Warning
**Fix:** Script `sys.database_permissions` joined to `sys.database_principals` and object/schema
names before migrating, and replay the explicit GRANT/DENY statements after the database and
users exist on the target — explicit grants are not implied by role membership and will be
silently lost otherwise.

### J9 — Cross-Database Ownership Chain Breaks at Migration Boundary
**Trigger:** A stored procedure or view in the migrated database references objects in a
different database via ownership chaining, and that second database is not in the same migration
scope or `TRUSTWORTHY`/ownership chaining settings differ on the target.
**Severity:** Warning
**Fix:** Identify cross-database ownership chains before migrating (`sys.sql_expression_dependencies`
filtered to cross-database references — `capture-security-facts.sql` Query 11 collects these per
database); either migrate both databases together or replace the
chain with an explicit permission grant plus a module signing certificate — do not enable
`TRUSTWORTHY ON` as a substitute, since it is a documented security risk.

## Category 3 — Credentials & Secrets

### J10 — SQL Server Credential Object Not Migrated
**Trigger:** Source has one or more `sys.credentials` entries (used by proxy accounts, CLR, or
external data sources), and the migration plan's script set does not include credential
recreation.
**Severity:** Critical
**Fix:** `CREATE CREDENTIAL` cannot be scripted with the underlying secret intact (SQL Server
does not expose stored secrets) — re-enter the credential's secret manually on the target using
`CREATE CREDENTIAL [name] WITH IDENTITY = '<identity>', SECRET = '<secret>';`, sourced from the
original secret store, not from the source instance.

### J11 — SQL Agent Proxy Account References a Credential Not Yet Created
**Trigger:** `msdb.dbo.sysproxies` references a credential by `credential_id`, and the migration
order creates the proxy before the credential exists on the target.
**Severity:** Warning
**Fix:** Sequence the migration script to create the credential (J10) before the proxy account,
and re-map `credential_id` by name rather than by ID, since IDs are not guaranteed to match
across instances.

### J12 — Linked Server Stored Login Not Migrated
**Trigger:** Source has `sys.linked_logins` mappings (impersonation or stored credentials for a
linked server), and the migration plan recreates the linked server definition without the
stored login mapping.
**Severity:** Warning
**Fix:** Script `sp_addlinkedsrvlogin` for each mapping captured in `sys.linked_logins`, supplying
the stored password again manually (it is not exposed by any system view) — see
`/sqlmigration-objects-review` for the rest of the linked server definition migration.

## Category 4 — Certificates & Keys

### J13 — Certificate-Backed Object Migrated Without Its Certificate
**Trigger:** Source has objects depending on a certificate (Service Broker, certificate-mapped
logins, module-signing certificates) per `sys.certificates`, and the certificate is not in the
migration plan's script set.
**Severity:** Critical
**Fix:** `BACKUP CERTIFICATE [certname] TO FILE = '<path>' WITH PRIVATE KEY (FILE = '<path>',
ENCRYPTION BY PASSWORD = '<password>');` on the source, then `CREATE CERTIFICATE ... FROM FILE
... WITH PRIVATE KEY (...)` on the target before the dependent objects are created. This excludes
TDE certificates — see `/sqlencryption-review` for the full TDE certificate migration sequence.

### J14 — Database Master Key Not Backed Up Before Migration
**Trigger:** Migrated database has `sys.symmetric_keys` showing a database master key
(`name = '##MS_DatabaseMasterKey##'`), and no `BACKUP MASTER KEY` step appears in the migration
plan.
**Severity:** Critical
**Fix:** `OPEN MASTER KEY DECRYPTION BY PASSWORD = '<password>'; BACKUP MASTER KEY TO FILE =
'<path>' ENCRYPTION BY PASSWORD = '<password>';` on the source before migrating, then restore it
on the target with `RESTORE MASTER KEY FROM FILE = '<path>' DECRYPTION BY PASSWORD = '<password>'
ENCRYPTION BY PASSWORD = '<newpassword>';` — see `/sqlencryption-review` for the full DMK/SMK key
hierarchy migration sequence if this database uses Always Encrypted or column-level encryption.

## Category 5 — CMS

### J15 — Central Management Server Registration Points at Old Instance Name
**Trigger:** The migration changes the instance name or the listener name the application
connects through, and CMS registrations on the CMS host (managed via SSMS Registered Servers,
backed internally by `msdb.dbo.sysmanagement_shared_registered_servers`) still reference the
pre-migration name.
**Severity:** Info
**Fix:** Update the CMS registration's server name via the SSMS Registered Servers GUI — this is
the Microsoft-documented mechanism and the one to use by default. **Unverified:** the underlying
`msdb.dbo.sysmanagement_shared_registered_servers` table and a
`sp_sysmanagement_update_shared_registered_server` procedure are referenced in community
documentation of CMS internals, but Microsoft Learn does not publicly document this schema or
any T-SQL procedure for scripting CMS registration changes — treat any T-SQL-based update to
these objects as unsupported and verify against a non-production CMS host before relying on it,
or confirm current behavior against the CMS host's actual schema before scripting.

## Output Format

```
## sqlmigration-security-review

### Summary
[2-3 sentences: login count, permission complexity, headline risk]

### Critical Issues
[J-checks with Critical severity — each as: **J# — Name**, evidence, impact, fix]

### Warnings
[J-checks with Warning severity]

### Info
[J-checks with Info severity, if any]

### Passed Checks
[J-checks evaluated and not fired]

### Not Assessed
[J-checks that could not be evaluated because the required input was not provided]

### Login/Permission Migration Script Checklist
[Ordered list of native-tool scripts to run: sp_help_revlogin output, role membership,
explicit grants, credentials, certificates, in dependency order]

---
Analyzed by: [model name] · [date/time]
```

## Output Filters

- `--brief` — Summary + Critical Issues only
- `--critical-only` — Critical Issues only

## Verbose Output (--verbose)

When `--verbose` is passed, also write
`output/sqlmigration-security-review/<timestamp>-J/analysis.md` (full report) and `trace.md`
(which facts were available, which J-checks were skipped and why).

## Notes

- This skill is strictly offline — it never connects to SQL Server; all checks evaluate pasted
  facts or capture-script output.
- TDE certificates and the full DMK/SMK key hierarchy are `sqlencryption-review`'s domain; J13/J14
  cover only the migration-script-sequencing risk, not the encryption posture itself.
- Dispatched here from `/sqlmigration-review` for the security-object family — not invoked
  standalone for non-migration security review (use `sqlencryption-review` for that).

## Companion Skills

- **`sqlmigration-review`** — parent skill; version/edition/platform compatibility (Y1–Y15)
- **`sqlmigration-objects-review`** — operational objects (Agent jobs, linked servers, Database
  Mail) for the rest of the proxy/linked-server definition beyond the stored login (J11/J12)
- **`sqlencryption-review`** — TDE, full DMK/SMK key hierarchy, Always Encrypted, CLE
- **`sqlspn-review`** — SPN/Kerberos delegation for the renamed/relocated instance