Take the 2-minute tour ×
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.

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 at 14:34

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

    
I got this exact message attempting to DROP a database that was offline while not sa-equivalent, if that's any help –  AakashM May 15 at 12:14

2 Answers 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

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 at 2:29
1  
@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 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 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.