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 |
|
jbates99
Constraint Violating Yak Guru
396 Posts |
 Posted - 2012-12-11 : 16:23:18
|
| We have far too many logins that can create databases.Which is better:Revoke CREATE DATABASE from UserX CASCADE;or Deny CREATE DATABASE from UserX CASCADE; ?I seem to remember that one of these will be removed at some point.Thanks, Jack |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-12-11 : 16:42:52
|
| They are not the same command. DENY specifically prevents someone from performing that action, even if they were GRANTed that permission via another means (role membership). REVOKE removes any GRANT or DENY on that permission for that user. Therefore, if you want to prevent them from creating databases, you have to use DENY.I would be extremely careful about using CASCADE unless you absolutely know that the entire grantor-grantee path is valid for that operation. |
 |
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2012-12-11 : 17:34:33
|
| Thank you, robvolk.I expected to be able to use DENY DROP DATABASE to userX but that fails. How can I deny use of drop database?Thanks. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-12-11 : 17:56:35
|
| I would think DENY CREATE ANY DATABASE should do it, and DENY ALTER ANY DATABASE may be necessary. Make sure they are removed from the sysadmin server role as well. |
|
|
|
|
|
|
Revoke or Deny?