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

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