Vault not dropping users in MSSQL

Vault
1

We have set up our Vault instance to create dynamic credentials in MSSQL, multiple databases per instance. The creation of the login and users works fine. In terraform this looks like:

  creation_statements = [
    "CREATE LOGIN [{{name}}] WITH PASSWORD = '{{password}}';",
    "USE FooDB;",
    "CREATE USER [{{name}}] FOR LOGIN [{{name}}];",
    "GRANT SELECT ON SCHEMA::dbo TO [{{name}}];",
    "USE BarDB;",
    "CREATE USER [{{name}}] FOR LOGIN [{{name}}];",
    "GRANT SELECT ON SCHEMA::dbo TO [{{name}}];",
    "USE FeeDB;",
    "CREATE USER [{{name}}] FOR LOGIN [{{name}}];",
    "GRANT SELECT ON SCHEMA::dbo TO [{{name}}];",
  ]

However, when the credentials expire, only the FooDB user and login are removed. The other two DB users are left orphaned and have to be manually cleaned up. FooDB happens to be the default DB.

Our Terraform for revocation looks like this:

  revocation_statements = [
    "USE FooDB",
    "DROP USER [{{name}}];",
    "USE BarDB",
    "DROP USER [{{name}}];",
    "USE FeeDB",
    "DROP USER [{{name}}];",
    "DROP LOGIN [{{name}}];",
  ]

I’ve tried with and without the semi-colon after the USE statement, based on this other discussion thread, but it doesn’t seem to make a difference.

Does anyone have any working examples of something like this where multiple DBs are involved?

FWIW this is Vault 1.17.5 and MS SQL Server 2022.