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?

score 23 · Answer 1 · edited Nov 10 '15 at 13:45

23

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

edited Nov 10 '15 at 13:45

Artjom B.

61,146
24
125
222

answered Nov 10 '15 at 13:44

baazi shaik

231
2
2

score 19 · Accepted Answer · answered May 24 '10 at 17:10

19

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

DROP DATABASE DatabaseName

answered May 24 '10 at 17:10

SQLMenace

132,095
25
206
225

4

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` – LockTar Apr 03 '14 at 05:45
1

That doesn't work for me. `Incorrect syntax was encountered while parsing GO.` – nwalke Sep 25 '14 at 16:00
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 – Leniel Maccaferri Feb 28 '15 at 02:44
@LockTar's command works perfectly, if you leave off the `GO` parts. Disconnect and reconnect to see the database gone from the Object Explorer. – Jonathan E. Landrum Aug 11 '21 at 20:52
Cannot drop database "Db_Name" because it is currently in use. – nafischonchol Aug 18 '22 at 13:54

score 2 · Answer 3 · answered Apr 04 '18 at 10:12

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!

How do I drop SQL Databases? sp_delete_database_backuphistory woes

3 Answers3