Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

enter image description here

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.

share|improve this question

migrated from serverfault.com Feb 3 '14 at 14:34

This question came from our site for system and network administrators.

up vote 4 down vote accepted

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
share|improve this answer
    
Any idea why autoclose causes the symptoms in the question? – Tim Abell Apr 7 at 15:25
    
You my friend have won the internet today. Take my money. – Jeff.Clark Jun 15 at 20:10
    
@TimAbell: The answer is complete now! – Learner Jun 16 at 12:20

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

share|improve this answer
    
I have indeed tried with SSMS directly on the server, making a new connection with this user's credentials. Same result as remotely. I've just checked and the user can indeed create a table, insert a row and read the row back successfully. I double-checked the correct user was showing before executing the query in ssms and it was, but after running the drop statement it changed to "not connected". I guess that's just because it's a fatal error but I hadn't noticed this before now. – Tim Abell Feb 2 '14 at 2:29
2  
@Tim: Yes, errors of level 20 and higher drop the connection, so that's just a symptom. Does the SQL Server Error Log or the Application Event Log give any more information? – Jon Seigel Feb 3 '14 at 18:17
    
I'm afraid I haven't had a chance to go back to this to check, but thanks for the tip. I shall check the logs next time I'm looking into this (not sure when that will be). – Tim Abell Feb 19 '14 at 21:08

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.