locked
Deny certain user from deataching a certain database in sql server 2005 RRS feed

  • Question

  • Dears,
    How can i deny a certain user from detaching certain database in sql 2005 ?
    for example denying sa from detaching adventureworks

    Best Regards,
    Ruben
    Wednesday, January 7, 2009 3:07 PM

Answers

  • Thing you can do is deny the user execute rights on sp_detach_db store procedure.
    But I guess rights on sp_detach_db are global rights, so you won't able to deny the user on particular database.  After denying execute rights on sp_detach_db, user won't be able to detach any database on Server.

    One more thing, sa is sysadmin, you can't deny any rights to sa login. 
    Mangal Pardeshi. You can turn off the SUN, but I'm still gonna shine. ;)
    Sunday, January 11, 2009 2:07 PM

All replies

  • Thing you can do is deny the user execute rights on sp_detach_db store procedure.
    But I guess rights on sp_detach_db are global rights, so you won't able to deny the user on particular database.  After denying execute rights on sp_detach_db, user won't be able to detach any database on Server.

    One more thing, sa is sysadmin, you can't deny any rights to sa login. 
    Mangal Pardeshi. You can turn off the SUN, but I'm still gonna shine. ;)
    Sunday, January 11, 2009 2:07 PM
  • So are you saying that you can not deny a particular user detach rights to a particular database?  Aside from sa and not being able to deny that user.  What about other users?  So if he posed the question:

     

    Deny the user WebAppServiceAccount the right to detach the database Bigdatabase, while still allowing WebAppServiceAccount the right to detach the databases named SmallDatabase1 thru 21.

     

    Is that possible?

    Thursday, April 14, 2011 3:41 PM
  • So are you saying that you can not deny a particular user detach rights to a particular database?  Aside from sa and not being able to deny that user.  What about other users?  So if he posed the question:

     

    Deny the user WebAppServiceAccount the right to detach the database Bigdatabase, while still allowing WebAppServiceAccount the right to detach the databases named SmallDatabase1 thru 21.

     

    Is that possible?


    As you are requiring the ability to detach a database at its scope you cannot grant permissions on sp_detach_db or alter any database server level permissions as they are applied either on all databases or none of them.

    The account must be a member of db_owner rights at each of the database to be able to detach or be the owner of database.


    Thanks, Leks
    Thursday, April 14, 2011 9:13 PM