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!

Delft

Answer 1

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.

Flatten

On top of this, I found that I needed to also reset the master database password via the AWS console. You can just set it to the exact same password, but only after doing this, did the rename work. – Flatten

I've had to do this a few times, and it appears that changing the master password is enough (without having to rename the db). Apparently when you change the password it updates all the dbs and assigns your master user to all the dbs. – Hither

resetting the master db password solved the same problem for me, thank you! – Arrowworm

I can confirm that, as of february 2020, a password reset is still required to be granted the permissions required for this action. – Irfan

Having exactly the same problem and after resetting the master password through the AWS RDS console we were able to delete the database. One thing to add is that if you are using an RDS Proxy, it will likely have a connection to the database, but using SSMS you can simply check the option to drop connections when deleting the database. – Rolfston

Answer 2

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

I ran into the same problem, but in my case, my database was in an 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

Adessive

Answer 3

If your database is in a Multi-AZ deployment, then you need to run this command to drop those databases:

EXECUTE msdb.dbo.rds_drop_database N'DBName'

Ferrule

This approach was working for me, but not the one with a higher rank. – Lozoya

Answer 4

Sounds like your not a member of the correct role.

https://msdn.microsoft.com/en-us/library/ee240822.aspx

Permissions

A DAC can only be deleted by members of the sysadmin or serveradmin fixed server roles, or by the database owner. The built-in SQL Server system administrator account named sa can also launch the wizard.

https://msdn.microsoft.com/en-us/library/ms178613.aspx

Permissions

SQL Server - Requires the CONTROL permission on the database, or ALTER ANY DATABASE permission, or membership in the db_owner fixed database role.

Azure SQL Database - Only the server-level principal login (created by the provisioning process) or members of the dbmanager database role can drop a database.

Parallel Data Warehouse - Requires the CONTROL permission on the database, or ALTER ANY DATABASE permission, or membership in the db_owner fixed database role.

Timepiece

Answer 5

Having exactly the same problem and after resetting the master password through the AWS RDS console we were able to delete the database using the master account. One thing to add is that if you are using an RDS Proxy, it will likely have a connection to the database, but using SSMS you can simply check the option to drop connections when deleting the database.

Rolfston

Answer 6

I know this is old, but as of now you can simply use the following command to successfully drop your database:

    --replace your-database-name with the name of the database you want to drop
    EXECUTE msdb.dbo.rds_drop_database  N'your-database-name'

Refer to the following link https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.CommonDBATasks.DropMirrorDB.html

Sponsor