SeaweedFS Filer Lookup Latency and Dir Count Sentinel
Audits SeaweedFS filer for metadata lookup latency, directory entry growth, and metastore connectivity.
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.
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.
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.
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.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.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.login_claimed and leaves the orphan alone. That re-map would either be rejected by the server or give two users one identity.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.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.dry_run is true and auto_remediate is false, so a normal run reads the catalog and alerts.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.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.ALTER USER does not abort the batch. The re-read decides, then fails the run.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 |
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.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.route (io.kestra.plugin.core.flow.If):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.errors posts a Slack alert on any failure, including the ceiling refusal and the re-verify failure.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.WITHOUT LOGIN or certificate users reports nothing.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.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.
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.
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.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.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.SECRET_.| 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 |
SQLSERVER_PASSWORD and SLACK_WEBHOOK_URL secrets.url to the database you want to check and set user.auto_remediate: true and dry_run: false, then resume the pause with approved: true.weekly trigger, which always runs in safe mode.sys.databases first and loop the audit over every database on the instance, since a restore orphans users one database at a time.sys.objects and sys.schemas for the principal, so the Slack alert says what the user owns before anyone approves.no_matching_login finding behind its own gate, which turns the one refusal a name match cannot fix into a two-step flow.Pause for a Slack approval task so the gate lives where the alert does.