Unable to destroy resources created with terraform when changing ownership

[ZigZag59]

Provider Version

snowflake = {
source = "Snowflake-Labs/snowflake"
version = "0.35.0"
}

Terraform Version

Terraform v1.2.2 on linux_amd64

Describe the bug

When executing terraform destroy, I get the following error :
Error: error dropping database MY_DB: 002003 (02000): SQL compilation error: Database 'MY_DB' does not exist or not authorized.

I create the database 'MY_DB' and the role 'MY_DB_DBA' then I grant the privilege 'OWNERSHIP' of the database to 'MY_DB_DBA'. Everything is OK with the terraform apply. But when I want to delete the ressources by executing 'terraform destroy', I get the error.

To grant the ownership of the database, I use the "SECURITYADMIN" role (Only "ACCOUNTADMIN" AND "SECURITYADMIN" can do it). But the revert action set the ownership to "SECURITYADMIN" instead of "SYSADMIN" and consequently, the "SYSADMIN" used to create the database cannot execute the DROP DATABASE.

Expected behavior

All resources created would be deleted.

Code samples and commands

terraform {
required_providers {
snowflake = {
source = "Snowflake-Labs/snowflake"
version = "0.35.0"
}
}
}

provider "snowflake" {
alias = "account_admin"
role = "ACCOUNTADMIN"
}

provider "snowflake" {
alias = "sys_admin"
role = "SYSADMIN"
}

provider "snowflake" {
alias = "security_admin"
role = "SECURITYADMIN"
}

resource "snowflake_role" "my_db_dba" {
provider = snowflake.security_admin
name = "MY_DB_DBA"
}

resource "snowflake_role_grants" "grant_my_db_dba" {
provider = snowflake.security_admin
role_name = "${snowflake_role.my_db_dba.name}"
roles = ["SYSADMIN"]
}

resource "snowflake_database" "my_db" {
provider = snowflake.sys_admin
name = "MY_DB"
}

resource "snowflake_database_grant" "grant_database_ownership" {
provider = snowflake.security_admin
database_name = "${snowflake_database.my_db.name}"
privilege = "OWNERSHIP"
roles = ["${snowflake_role.my_db_dba.name}"]
with_grant_option = true
}

[clairemermet]

I'm facing exactly the same issue. How can we modify the fact that "revert action set the ownership to "SECURITYADMIN". It is really blocker for my project, who can help? Perhaps with a workaround at least?

[clairemermet]

I tested again with the last release 0.47.0. Same problem than with the 0.35.0 that @ZigZag59 used:

• I created a Snowflake database for example with terraform
• This database is owned by SYSADMIN
• If I launch a destroy, the provider Snowflake-Labs change the owner to ACCOUNTADMIN. Terraform can't destroy this ressource with SYSADMIN as now it is owned by ACCOUNTADMIN

It is really a blocker bug, is it possible to work on it asap?

If needed, I can provide example to reproduce it easily.

Pieces of code used:

terraform {
required_providers {
snowflake = {
source = "Snowflake-Labs/snowflake"
version = "0.47.0"
}
}

resource "snowflake_database" "db_src" {
count = var.no_of_users
provider = snowflake.sys_admin
name = join("", [var.snowflake_db_prefix, data.aws_caller_identity.current.account_id, format("USER%02d", count.index + 1)])

data_retention_time_in_days = 0

}

resource "snowflake_database_grant" "database_grant_ownership" {
count = var.no_of_users
provider = snowflake.account_admin
database_name = snowflake_database.db_src[count.index].name
privilege = "OWNERSHIP"
roles = ["SYSADMIN"]
with_grant_option = true

depends_on    = [snowflake_database.db_src]

}

After deploy, all is good, owner is SYSADMIN as expected:

Then during destroy, the provider change it for ACCOUNTADMIN and I encounter error for destroy as:

Because the database can't be accessed with SYSADMIN now.