/sqlmigration-security-review

name: sqlmigration-security-review
description: 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.
triggers:
  - /sqlmigration-security-review
  - /migration-security-review
  - /sql-migration-logins

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):

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 LO