db_owner unable to drop database - Error 615, SQL Server

Question

I have a SQL Server 2012 database running on amazon EC2. I've created a user to be able to create, edit and drop databases. I gave the new user with dbcreator server role.

My user can connect remotely and successfully runs the create database foo; command. But when the user attempts to drop the database again with drop database foo; the command fails with the following error:

Warning: Fatal error 615 occurred at Feb  1 2014  5:15PM.
   Note the error and time, and contact your system administrator.
ErrorCode: 21

Even though the selected database is master (so I don't think it's because it's in use). And the command succeeds when I run it again logged in as an administrative user.

I checked the newly created database, and my user has been assigned the db_owner role in the database as I expected so my understanding is that this should be sufficient permissions for that user to be able to drop the database they just created.

According to http://technet.microsoft.com/en-us/library/ms178613.aspx the db_owner role should be sufficient permissions. "Requires the CONTROL permission on the database, or ALTER ANY DATABASE permission, or membership in the db_owner fixed database role."

I've looked up error 615 and found "Could not find database table ID %d, name '%.*ls'." which makes no sense to me. http://technet.microsoft.com/en-us/library/aa937592(v=sql.80).aspx

Sql server version info: Microsoft SQL Server 2012 (SP1) - 11.0.3368.0 (X64) /n May 22 2013 17:10:44 /n Copyright (c) Microsoft Corporation/n Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)/n - from select @@version.

Answer 1

I can guess you have the AutoClose option for database set to True. This is the default behavior when you create a database with Express Editions.

The mentioned error can occur exactly in this case. Actually the complete error message 615 states: "Could not find database ID %d, name '%.*ls'. The database may be offline. Wait a few minutes and try again." ... So it points that database could be closed during dropping.

So, go to DB properties, switch it to False and try again dropping it or use below script before dropping

ALTER DATABASE [MyDB] SET AUTO_CLOSE OFF 
GO

Many point out that is better to have AutoClose set to False. I found this article explaining a bit more about AutoClose: http://sqlmag.com/blog/worst-practice-allowing-autoclose-sql-server-databases

Small extension of the answer:

-- this works in standard SQL Server Editions, but NOT with Express Editions:
CREATE DATABASE [MyDB]
GO
DROP DATABASE [MyDB]
GO

-- this works in ALL SQL Server Editions
CREATE DATABASE [MyDB]
GO
ALTER DATABASE [MyDB] SET AUTO_CLOSE OFF 
GO
DROP DATABASE [MyDB]
GO

Answer 2

The configuration you describe should be fine.

Is there any chance that you're actually attempting to drop the database as a different user?

I'd recommend connecting as the test user using SSMS to be 100% sure that you are that user. Also, before dropping the database check you can access some data from it, maybe also test you can put it in to single user mode which I guess you'll need to do ultimately anyway

Answer 3

I did not find the cause for this, but the following while not best solution does enable command.

It is my hope it points to the problem and someone can provide a better answer.

I use Microsoft SQL Server Management Studio (Administrator) Under localhost Security logins

Double click user, select Server Roles, give dbcreator, public, and sysadm.