How do I drop SQL Databases? sp_delete_database_backuphistory woes

Question

I want to delete some SQL Databases on my server, but I'm having problems. My login has the roles:

• public
• dbcreator
• serveradmin

When I right click the database and hit Delete, it says that

Delete backup history failed for server 'MYSERVER' (Microsoft.SqlServer.Smo)
Additional Information: The EXECUTE permission was denied on the object 'sp_delete_database_backuphistory'

How do I delete these databases?

Copland

Answer 1

do you get the same problem when executing it from a query window like this?

DROP DATABASE DatabaseName

Isolation

If you get the an error that there is a connection in use then select the master database as executing database. Or use this code: USE Master; GO DROP DATABASE AdventureWorks; GO – Brunell

That doesn't work for me. Incorrect syntax was encountered while parsing GO. – Theological

Sometimes I'm amazed how things work out of logic. Using the delete database UI in SSMS doesn't work but executing the command it works! Amazing really! ;) I'd never think about doing this... hehehe – Etz

@LockTar's command works perfectly, if you leave off the GO parts. Disconnect and reconnect to see the database gone from the Object Explorer. – Hastie

Cannot drop database "Db_Name" because it is currently in use. – Anorak

Answer 2

Please uncheck the "Delete Backup and restore history information for databases" then click the OK button.

Executive

Answer 3

The only thing that worked for me was to:

1) Run SSMS as Administrator

2) Login using the master user(which would be sa in most cases). This is the user that you specified when installing Sql Server.

After the above mentioned steps, I was able to delete the database even without unchecking the backup checkbox!

Repetition