none
GRANTing rights to DBCC detachdb?

    Question

  • Hi,

    Do you know how we can grant a user who is db_owner in a database and has the dbcreator server role rights to run DBCC detachdb [( 'dbname' )]? We need to allow non-sysadmins th ability to run sp_detach_db.

    The error the user gets running sp_detach_db is:
    Msg 7983, Level 14, State 36, Line 1
    User 'public' does not have permission to run DBCC detachdb for database 'DBNameSample'.

    TIA,
    Dave

    • Edited by Dave Satz Tuesday, March 23, 2010 4:56 PM more info
    Tuesday, March 23, 2010 4:41 PM

Answers

  • No problem... This is not possible, I am affraid. If someone is not the owner of a database, he can not detach the database from an instance of SQL Server. It actually means that the database is removed from the server (deleted from master database even if the database files are left on the disk).

    In addition, as far as I know, there is no standard way to remove the creator of a database, who becomes the owner, from db_owner role. The only way is to detach the database and attach it with different owner.

    MP.

    • Marked as answer by Dave Satz Tuesday, March 23, 2010 9:06 PM
    Tuesday, March 23, 2010 8:58 PM

All replies

  • Hi,

    I tried to detach a db from my server under user which was an owner of the database and did not have any permissions on the server level with the exception of connect. The detach operation was successful... Is your login really member of db_owner database role?

    MP

    Tuesday, March 23, 2010 5:42 PM
  • Yes, my login/user is db_owner through an AD group.

    More info: version: Microsoft SQL Server 2008 (SP1) - 10.0.2740.0 (X64)   Oct 24 2009 15:50:23  

    Thank you for the quick response.

     

    Tuesday, March 23, 2010 6:24 PM
  • Embarassed  

    I was not db_owner, when I made the correct AD group db_owner (I have two AD accts), I was able to detach/attach.

    What were are really trying to do is:

    1. let people create databases using dbcreator server role
    2. remove their db_owner permissions
    3. still allow the same login/user to detach the database they created

    Sorry for the confusion

     

    Tuesday, March 23, 2010 7:50 PM
  • No problem... This is not possible, I am affraid. If someone is not the owner of a database, he can not detach the database from an instance of SQL Server. It actually means that the database is removed from the server (deleted from master database even if the database files are left on the disk).

    In addition, as far as I know, there is no standard way to remove the creator of a database, who becomes the owner, from db_owner role. The only way is to detach the database and attach it with different owner.

    MP.

    • Marked as answer by Dave Satz Tuesday, March 23, 2010 9:06 PM
    Tuesday, March 23, 2010 8:58 PM
  • Thanks for the response

    EXEC sp_changedbowner 'sa'

    will change the owner

    Tuesday, March 23, 2010 9:06 PM
  • Oh yes, I can see, there is an option... Thank you, Dave. I am a little bit more clever now :-).

    MP.

    Tuesday, March 23, 2010 10:14 PM
  • As of 2005, the recommended way to change onwer (for any type of object) is the ALTER AUTHORIZATION command.


    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, March 24, 2010 7:06 AM
  • As of 2005, the recommended way to change onwer (for any type of object) is the ALTER AUTHORIZATION command.


    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi

    Thank you Tibor!
    Wednesday, March 24, 2010 12:24 PM