Invoke-sqmDatabaseStandardization
Maintenance
sqmSQLTool v1.9.149+ · Invoke
✓ -WhatIf supported
Each step can be switched off on its own, every change honours
-WhatIf, and each run writes a CSV change log. A second run on the same database only reports Skipped.
Invoke-sqmRestoreDatabase calls this function after every successful restore. Use
-KeepCompatibilityLevel there to leave the level as it came out of the backup.
The five steps
| Step | What happens | Switch off with |
|---|---|---|
FixOrphanUser | A user whose SID matches no login, but for which a login with the same name exists, is re-mapped to it with ALTER USER ... WITH LOGIN. This is the classic case after a restore onto another server: the SQL login exists there too, just with a different SID. | -SkipOrphanRepair |
RemoveUserWithoutLogin | Users whose SID still matches no login are dropped. Schemas and roles owned by the user are handed to dbo first, otherwise DROP USER fails. Handover and drop run in one transaction: if the drop fails, the ownership stays as it was and the row is reported as Failed. | -SkipUserRemoval |
CompatibilityLevel | Raised to the level of the server version (SQL Server 2022 = 160, 2025 = 170). A level that is already higher is never lowered. | -SkipCompatibilityLevel |
TargetRecoveryTime | TARGET_RECOVERY_TIME = 60 SECONDS (indirect checkpoint, the default for databases created since SQL Server 2016). Configurable via -TargetRecoveryTimeSeconds. | -SkipTargetRecoveryTime |
DatabaseOwner | Owner set to the sa account, found by SID 0x01, so it also works when sa has been renamed. | -SkipOwner |
Compatibility level: a new level can change execution plans, mainly through the newer cardinality estimator. For an application that is not yet certified for it, run with
-SkipCompatibilityLevel and raise the level separately after a performance check.Users that are never dropped
| User | Why it stays |
|---|---|
dbo, guest, INFORMATION_SCHEMA, sys, ##...## | Built-in and internal principals. |
Created WITHOUT LOGIN | Deliberately login-less: Service Broker, module signing, EXECUTE AS targets. Kept unless you pass -IncludeUsersWithoutLogin. |
| Contained database users, certificate and asymmetric key users | They never had a server login to begin with. |
| Windows users that reach the server through a group login | The user has no login of its own, but its account is a member of a Windows group that has one. Then the database user is the permission, and dropping it would lock out a working account. Checked with xp_logininfo ... 'all'; the row is reported as Skipped with the group that grants the access. |
Parameters
| Parameter | Type | Required | Default | Notes |
|---|---|---|---|---|
| -SqlInstance | string[] | Optional | $env:COMPUTERNAME | One or more instances. Accepts pipeline input. |
| -SqlCredential | PSCredential | Optional | , | SQL authentication instead of the current Windows context. |
| -Database | string[] | Optional | all user databases | Database names, wildcards allowed (App*). |
| -ExcludeDatabase | string[] | Optional | , | Databases to leave out, wildcards allowed. |
| -TargetRecoveryTimeSeconds | int | Optional | 60 | Value for TARGET_RECOVERY_TIME, 1 to 3600. |
| -IncludeUsersWithoutLogin | switch | Switch | $false | Also drop users that were created WITHOUT LOGIN. |
| -SkipOrphanRepair | switch | Switch | $false | Skip step 1. |
| -SkipUserRemoval | switch | Switch | $false | Skip step 2. |
| -SkipCompatibilityLevel | switch | Switch | $false | Skip step 3. |
| -SkipTargetRecoveryTime | switch | Switch | $false | Skip step 4. |
| -SkipOwner | switch | Switch | $false | Skip step 5. |
| -OutputPath | string | Optional | <OutputPath>\DatabaseStandardization | Directory for the CSV change log. Written only when a row is OK or Failed, also after an abort. |
| -ContinueOnError | switch | Switch | $false | Carry on with the next instance if one cannot be reached. |
| -EnableException | switch | Switch | $false | Throw on the first failure instead of returning a Failed row. |
| -WhatIf / -Confirm | switch | Optional | , | ShouldProcess per single change, ConfirmImpact = 'Medium'. Under -WhatIf a user that step 1 would re-map is not also listed as "would be dropped" in step 2. |
Execution Flow
Result
One row per action with SqlInstance, Database, Step, Target (user or database name), OldValue, NewValue, Status and Message. A database that is skipped as a whole gets a single row with Step = Database.
| Status | Meaning |
|---|---|
OK | The change was made. |
Skipped | Nothing to do (already at the target value), or deliberately left alone: a Windows user with group access, or a database that is offline, read-only or an AG secondary. |
Failed | The change failed. The message carries the SQL Server error, for example a user that still owns objects other than schemas and roles, or a login that is already mapped to another user in the same database. |
WhatIf | -WhatIf: this is what would have happened. |
Examples
Dry run across all user databases of an instance
Invoke-sqmDatabaseStandardization -SqlInstance "SQL01" -WhatIf |
Format-Table Database, Step, Target, OldValue, NewValue, Status -AutoSizeOne database after a migration
Invoke-sqmDatabaseStandardization -SqlInstance "SQL01" -Database "SalesDB"
Everything except the compatibility level (application not yet certified for it)
Invoke-sqmDatabaseStandardization -SqlInstance "SQL01" -Database "App*" -SkipCompatibilityLevel
Also drop users created WITHOUT LOGIN, but leave the owner alone
Invoke-sqmDatabaseStandardization -SqlInstance "SQL01" -Database "SalesDB" `
-IncludeUsersWithoutLogin -SkipOwnerSeveral instances without prompting, show only what went wrong
"SQL01", "SQL02" | Invoke-sqmDatabaseStandardization -Confirm:$false |
Where-Object Status -eq "Failed" |
Format-Table SqlInstance, Database, Step, Target, Message -Wrap