AWS RDS SQL Server unable to drop database

Question

I tried to migrate a SQL Server database by Export Data-tier Application (.bacpac file) from an Amazon RDS instance to other, but import didn't succeed. So now I want to delete the database (which is empty), when I try to:

DROP DATABASE mydatabase;

I get the error:

Cannot drop the database 'mydatabase', because it does not exist or you do not have permission

Some context:

• I've tried using SQL Server Management Studio, and choosing close connections: same error.
• I'm logged as master user.
• I can create and drop other databases, but not this one.
• I just have these effective permissions on this database: CONNECT, SHOWPLAN, VIEW DATABASE STATE, VIEW DEFINITION (don't know why or how is this possible).

Any help is greatly appreciated!

Answer 1

This is the answer for an old thread but who knows, it might help someone having the same issue.

I run into the same problem, but in my case, my database was in offline mode. If the database is in offline mode, it won't allow you to drop it with the drop command. first, you should bring the database back online by running this sp and then execute the drop table command.

EXEC rdsadmin.dbo.rds_set_database_online databasename

Answer 2

I ran into this same issue. After trying to restore a database via SSMS using a .bacpac, it fails and leaves you with a database that you appear to not have permissions to drop.

A workaround, is to use the rdsadmin rename function to rename it to something else, which then seems to fix the permission issue and allows you to drop it.

EXEC rdsadmin.dbo.rds_modify_db_name N'<OldName>', N'<NewName>'

Then just drop the DB. Hope that helps someone else in the same predicament.