none
Deleting a database in RESTRICTED MODE RRS feed

  • Question

  • Please advice me how can i DELETE a database that is in RESTRICTED mode (Single User).

    I am not having access to that database now to be able to delete it. There should be some way to achieve this.
    Thursday, May 28, 2009 5:44 AM

Answers

  • The error message is prety clear: The EXECUTE permission was denied on the object 'sp_delete_database_backuphistory'

    I.e., the login yuou are using do not have execute permission on that stored procedure in the msdb database. Either skip the GUI and use the DROP DATABASE command (which won't attempt to remove backup history for this database), or have the DBA grant you those permissions.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by RubSay Thursday, May 28, 2009 10:38 AM
    Thursday, May 28, 2009 10:12 AM
    Moderator

All replies

  • Having single user for a database doesn't in itself prohibit dropping the database, as below script show. There has to be something else going on...

    CREATE DATABASE x
    GO
    ALTER DATABASE x SET SINGLE_USER
    GO
    DROP DATABASE x


    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Thursday, May 28, 2009 6:16 AM
    Moderator
  • Yes,
    I agree with Tibork. Though Databas is in Single User Mode or Restricted user mode we can delete(Drop) the database by rightclicking on that database and Delete. Restrict Access state does not stop us to delete the Database.

    Thanks
    Ramesh.M
    Thursday, May 28, 2009 6:20 AM
  • Hmmm....Well my database is in Single user mode and when i rick click and try to delete it, it says. Kindly advice how to overcome this.


    TITLE: Microsoft SQL Server Management Studio ------------------------------
    Delete backup history failed for Server 'moqsrvhq001\BkupExec'. (Microsoft.SqlServer.Smo) 

    ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------

    The EXECUTE permission was denied on the object 'sp_delete_database_backuphistory', database 'msdb', schema 'dbo'. (Microsoft SQL Server, Error: 229)
    • Edited by RubSay Thursday, May 28, 2009 10:12 AM
    Thursday, May 28, 2009 10:06 AM
  • The error message is prety clear: The EXECUTE permission was denied on the object 'sp_delete_database_backuphistory'

    I.e., the login yuou are using do not have execute permission on that stored procedure in the msdb database. Either skip the GUI and use the DROP DATABASE command (which won't attempt to remove backup history for this database), or have the DBA grant you those permissions.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by RubSay Thursday, May 28, 2009 10:38 AM
    Thursday, May 28, 2009 10:12 AM
    Moderator
  • Thanks Tibor and Ramesh. Yes, db in restricted mode has no effect on its deletion. I learned that its actually some issue of change in domains. Thanks anyways.
    Thursday, May 28, 2009 10:36 AM
  • Thanks Tibor and Ramesh. Yes, db in restricted mode has no effect on its deletion. I learned that its actually some issue of change in domains. Thanks anyways.

    Thursday, May 28, 2009 10:37 AM
  • RubSay. How did you resolve this issue? I also have a similar issue and it started to show after I changed the hostname of the machine. I'm not connected to a domain but am in workgroup.
    prateek
    Friday, May 6, 2011 7:57 PM
  • sorry i dont remember it now Prateek....what exactly i had done to resolve this....!!
    Sunday, May 8, 2011 9:50 AM