Geek Answers Handbook

AWS RDS SQL Server cannot delete database

I tried moving the SQL Server database using the DataTier Application ( .bacpacfile) from an Amazon RDS instance to another, but the import failed. So now I want to delete the database (which is empty) when I try:

DROP DATABASE mydatabase;

I get an error message:

Cannot delete mydatabase because it does not exist or you do not have permission

In some context:

  • I tried using SQL Server Management Studio and choosing tight connections: same error.
  • I am registered as the main user.
  • I can create and delete other databases, but not this one.
  • I only have these effective permissions to the database: CONNECT, SHOWPLAN, VIEW DATABASE STATE, VIEW DEFINITION(I do not know why and how this is possible).

Any help is much appreciated!

+7
source share
4 answers

I ran into this problem. After trying to restore the database via SSMS using .bacpac, it fails and leaves you with a database in which you do not have delete rights.

A workaround is to use the rdsadmin rename function to rename it to something else, which seems to fix the resolution problem and allow you to abandon it.

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

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

+8
source

It seems that you are not a member of the right role.

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

sysadmin serveradmin . SQL Server sa .

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

SQL Server - CONTROL ALTER ANY DATABASE db_owner.

Azure SQL - ( ) dbmanager .

Parallel Data Warehouse - CONTROL ALTER ANY DATABASE db_owner.

0

, - .

I ran into the same problem, but in my case my database was offline. If the database is offline, it will not allow you to delete it with the drop command. you must first bring the database online by running this sp, and then execute the delete table command.

EXEC rdsadmin.dbo.rds_set_database_online databasename
0
source

If your database is in a Multi-AZ deployment, you need to run this command to delete these databases:

EXECUTE msdb.dbo.rds_drop_database N'DBName'
-1
source

More articles:


All Articles