Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rmano1980
Starting Member
3 Posts |
 Posted - 2014-03-05 : 18:26:26
|
| Hi All,I have a created custom Database Role called Managers. This Role is member of db_owner role. I would like to remove the DROP Database and Restore Database privilege to this custom Role.How do I do this?Man |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-03-05 : 20:12:51
|
| Wouldn't you need to remove db_owner and then add the permissions you want? I don't think you can revoke drop/restore from db_owner.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
rmano1980
Starting Member
3 Posts |
Posted - 2014-03-05 : 21:11:02
|
| thanks Tara.I was thinking of adding db_owner to the Custom Role. Then run a DENY DROP Database Command to the custom role. But I believe there is no DENY DROP database command ? Is that correct ? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-03-05 : 22:18:50
|
| That was my point. I don't think you can do that. You probably can't use db_owner.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2014-03-06 : 13:19:53
|
| Grant the following for your custom role:db_datareaderdb_datawriterdb_ddladminAlso grant the following:GRANT EXECUTE ON schema::dbo TO {your custom role};And also grant execute on any other schemas in the database.This will give them pretty much everything except the ability to drop/restore databases. I would recommend that you test this out to make sure... |
|
|
|
rmano1980
Starting Member
3 Posts |
Posted - 2014-03-06 : 23:31:01
|
| thanking all of you for the insight. |
|
|
|
|
|
|
Custom Database Role