Query icon
OutputValues icon
Log icon
If icon
SlackIncomingWebhook icon
Fail icon
Pause icon
Loop icon
Schedule icon

SQL Server Orphaned Database User Guard

Detect SQL Server database users whose SID resolves to no login, alert Slack, then re-map by exact name behind a gate and re-read to confirm.

Categories
DataInfrastructure

Restore or attach a database onto a different instance and its users come with it. Their SIDs do not. A database user carries the SID of the login it was created from, the new server's logins have their own SIDs, so the user now resolves to nothing. That user is orphaned. It still exists inside the database, it still owns schemas and objects, it still holds every permission that was granted to it. No login can connect as it. Nothing errors until an application tries. Then the failure looks like a bad password on a login that is plainly there.

This blueprint reads sys.database_principals for the connected database, keeps the users that are meant to map to a server login, drops the ones that legitimately have none, then flags every user whose SID matches no row in sys.server_principals. When you allow it and approve the pause, it re-maps each flagged user with ALTER USER ... WITH LOGIN, one statement at a time, then reads the catalog again and fails the run if any user it tried to fix is still orphaned.

This blueprint was created by zkasuran.

Safety

Re-mapping an orphaned user to the wrong login is a privilege escalation that leaves no error behind. The user keeps every permission it was granted. Whoever holds that login now has them. SQL Server will not stop you: ALTER USER u WITH LOGIN = anything succeeds whenever the login exists. So this flow never guesses.

  • It re-maps by exact name only. A user is a candidate for ALTER USER only when a server login exists whose name is equal to the user's name and whose principal type matches the user's. No similarity, no prefix, no manual mapping table, no flow input that could name a login.
  • It requires exactly one candidate. The flow refuses unless the candidate count is exactly 1. sys.server_principals.name is unique, so a name match normally resolves to one login, but the check is on the count rather than on that assumption. It has to be: in a database whose collation is looser than the server's, one user name really does match two logins. An accent-insensitive database on an accent-sensitive server matches both jose and josé. The count check is what stops the flow picking one.
  • The name comparison carries COLLATE DATABASE_DEFAULT. sys.database_principals.name uses the database collation while sys.server_principals.name uses the server collation, so comparing them raises Cannot resolve the collation conflict in any database whose collation differs from the server's. That is exactly the database a cross-instance restore leaves behind, which is this flow's own use case.
  • It refuses a login that is already taken. When the matching login is already mapped to a different user in the same database, the flow reports login_claimed and leaves the orphan alone. That re-map would either be rejected by the server or give two users one identity.
  • It leaves users that have no login by design alone. A user created WITHOUT LOGIN, a certificate or asymmetric key mapped user and a contained database user all show up as having no matching server login. They are excluded by authentication_type and type, not by a name list, so none of them is ever flagged or touched.
  • It has a per-run ceiling. max_remap caps one run. Over the ceiling the run refuses the whole batch and names every user, instead of re-mapping part of it.
  • Read-only by default. dry_run is true and auto_remediate is false, so a normal run reads the catalog and alerts.
  • A Pause sits in front of the write. Resuming with approved false leaves every user orphaned. So does killing the execution. An unanswered pause reads as a refusal.
  • Statements are built in SQL with QUOTENAME from catalog values, so identifiers are quoted by the server. Each one runs as its own autocommitting io.kestra.plugin.jdbc.sqlserver.Query with concurrencyLimit: 1.
  • A refused ALTER USER does not abort the batch. The re-read decides, then fails the run.

Findings

Each row carries the user, its type, its unresolved SID, the candidate login and a status:

Status Meaning Action
remappable exactly one server login of the same name and matching type, not mapped to another user here re-mapped behind the gate
no_matching_login no server login of that name exists reported, never touched. Create the login, then run again
ambiguous_login more than one login matched the name reported, never touched
login_claimed the matching login already belongs to another user in this database reported, never touched

How it works

  1. audit (io.kestra.plugin.jdbc.sqlserver.Query, read-only) reads sys.database_principals and keeps users where type is one of S, U, G, authentication_type is 1 (INSTANCE, meaning the user is meant to map to a server login), principal_id > 4, is_fixed_role = 0 and the SID matches no row in sys.server_principals. It then joins sys.server_principals on an exact name and type match to count candidate logins, checks whether the candidate is already mapped to another user, sets the status and builds the ALTER USER ... WITH LOGIN statement with QUOTENAME for the remappable ones.
  2. plan (io.kestra.plugin.core.output.OutputValues) splits the findings into the remappable statements and the refused users. summary (io.kestra.plugin.core.log.Log) logs every user with its status, its SID and its candidate login.
  3. route (io.kestra.plugin.core.flow.If):
    • clean: logs that every user resolves to a login.
    • findings: alert posts the remappable and the refused lists to Slack.
      • gate runs only when auto_remediate is true, dry_run is false and at least one user is remappable. Otherwise no_change logs what was found and what blocked the write.
      • ceiling_gate fails the run through ceiling_exceeded when the remappable count is over max_remap.
      • approve (io.kestra.plugin.core.flow.Pause) waits for approved: true.
      • remediate (io.kestra.plugin.core.flow.Loop, concurrencyLimit: 1) runs one ALTER USER per user.
      • reaudit re-reads the catalog. verify_result fails the run through remediation_failed if any user is still orphaned with a matching login, which is what a refused write looks like.
  4. errors posts a Slack alert on any failure, including the ceiling refusal and the re-verify failure.

What you get

  • Every orphaned user in the database, with the SID that resolves to nothing, so the application login failure nobody could explain has a cause.
  • A clear split between the users a name match can fix and the users that need a human, with the reason for each refusal.
  • An ALTER USER ... WITH LOGIN re-map that only ever targets a login of the same name, behind two inputs, a ceiling and an approval pause.
  • A re-read of the catalog after the change that fails the run if a user it tried to fix is still orphaned, so a re-map the server accepted and ignored is never reported as success.
  • Users that have no login on purpose left untouched, so running this on a database full of WITHOUT LOGIN or certificate users reports nothing.
  • A Slack alert on findings and a separate alert on failure.

Who it's for

  • DBAs who restored a database onto a new instance and want the orphaned users found before an application finds them.
  • Platform teams running refresh pipelines from production into staging, where every refresh re-orphans the same users.
  • Anyone who has reached for sp_change_users_login 'Auto_Fix', which is deprecated and maps by name with no ambiguity check, who want the same fix with the checks plus the audit trail.
  • Teams evaluating the SQL Server plugin who want an example that reads the catalog and drives a permission change.

Why orchestrate this with Kestra

Listing orphaned users is one catalog query. Fixing them safely is not. The fix is a permission change, so it needs the candidate resolved from the catalog rather than typed in, a refusal on anything ambiguous, a ceiling so one bad restore does not re-map fifty users unattended, a human in front of the write, then a read-back that fails loudly when the server accepted the statement and changed nothing. Kestra holds all of that in one file: the audit, the Slack alert, the gate, the pause, the serial loop, the re-read and the failure. Every run keeps its own before and after in the execution history, which is what you want when the question is who was given which permissions and when.

Tested end to end

On Kestra 2.0.5 against SQL Server 2022 on Linux, with a local HTTP sink standing in for Slack. The degraded state was built the way production builds it: four logins plus matching users in a source database, a full backup, the database and the logins dropped, three of the four logins recreated so their SIDs changed, then a restore under a new name. sp_change_users_login 'Report' confirmed four orphaned users before any run.

Run Inputs Result
1 defaults against a clean database 0 orphaned users, clean branch
2 defaults against the restored database 4 orphaned users found, 2 remappable, 2 refused (no_matching_login, login_claimed). Slack alert "No change applied". The catalog was unchanged afterwards
3 auto_remediate: true, dry_run: true same 4 findings, still no write. The dry run blocked it with auto_remediate on
4 auto_remediate: true, dry_run: false, max_remap: 1 ceiling_exceeded failed the run at 2 remappable over a ceiling of 1, before the pause. Nothing re-mapped, errors posted to Slack
5 auto_remediate: true, dry_run: false, pause resumed with approved: false declined logged, both users still orphaned in the catalog
6 auto_remediate: true, dry_run: false, pause approved 2 ALTER USER ... WITH LOGIN ran, reaudit returned 0 remappable, the catalog showed both SIDs resolving to their logins. Both logins then connected to the database, one of them still owning its schema
7 defaults, after run 6 2 orphaned users, both refused, 0 remappable
8 defaults against a database whose collation is accent insensitive, with two server logins differing only by an accent one user matched both logins, ambiguous_login, nothing touched
9 auto_remediate: true, dry_run: false, pause approved, database set READ_ONLY WITH ROLLBACK IMMEDIATE the ALTER USER was refused, the loop still reported success, reaudit found the users still remappable and remediation_failed failed the run. errors posted to Slack
10 unreachable server audit failed, errors posted to Slack

The users that have no login by design were present throughout: a WITHOUT LOGIN user and a certificate mapped user, both of which a plain SID join reports as orphaned. Neither was ever flagged.

Prerequisites

  • A SQL Server database you can reach over JDBC.
  • A login that can read sys.database_principals and sys.server_principals. Reading every server login needs VIEW ANY DEFINITION or VIEW SERVER STATE, otherwise the server only returns the connected login and healthy users look orphaned. sysadmin covers it.
  • For the re-map, ALTER ANY USER on the target database, so db_owner is enough, as is sysadmin. Re-mapping to a login the caller does not control also needs IMPERSONATE on that login or membership of sysadmin.
  • A Slack incoming webhook.

Secrets

  • SQLSERVER_PASSWORD: password for the login in user. The password input defaults to this secret.
  • SLACK_WEBHOOK_URL: Slack incoming webhook, used by alert and the errors block.
  • In Kestra OSS, provide each one as a base64-encoded environment variable prefixed SECRET_.

Inputs

Input Default Purpose
url jdbc:sqlserver://localhost:1433;databaseName=app;encrypt=false Database to check. The queries read the connected database
user sa SQL Server login
password {{ secret('SQLSERVER_PASSWORD') }} Password for the login
max_remap 5 Ceiling per run. Over it the whole batch is refused
auto_remediate false Allow the ALTER USER statements
dry_run true Must be false for a statement to run

Quick start

  1. Add the SQLSERVER_PASSWORD and SLACK_WEBHOOK_URL secrets.
  2. Import the flow, then set url to the database you want to check and set user.
  3. Run it with the defaults and read the findings. Check each candidate login name against the user name yourself.
  4. When you are ready, run with auto_remediate: true and dry_run: false, then resume the pause with approved: true.
  5. Enable the weekly trigger, which always runs in safe mode.

How to extend

  • Point it at sys.databases first and loop the audit over every database on the instance, since a restore orphans users one database at a time.
  • Add the owned objects per orphan by reading sys.objects and sys.schemas for the principal, so the Slack alert says what the user owns before anyone approves.
  • Create the missing login for a no_matching_login finding behind its own gate, which turns the one refusal a name match cannot fix into a two-step flow.
  • Run it as the last task of a restore pipeline so a refreshed staging database never reaches a developer orphaned.
  • Swap the Pause for a Slack approval task so the gate lives where the alert does.

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.