AWS RDS SQL Server unable to drop database

sql-server database amazon-web-services amazon-rds
edumen picture edumen · Dec 4, 2014 · Viewed 8.5k times · Source

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

Kyle Whittington picture Kyle Whittington · Jun 7, 2017

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.

Related questions

Reset identity seed after deleting records in SQL Server

I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each table has to have …

Read More
How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More
How to replace a string in a SQL Server Table Column

I have a table (SQL Sever) which references paths (UNC or otherwise), but now the path is going to change. In the path column, I have many records and I need to change just a portion of the path, but …

Read More