We allow our users to be dbo in their own databases, however this does give them the ability to drop their own databases. This doesn't happen very often, but I want to prevent them from doing this.

I've devised the following trigger that prevents this happening and it restricts the dropping of databases to sysadmin role members:

CREATE TRIGGER [deny_customer_db_drop]
FOR DROP_database
IF IS_SRVROLEMEMBER('sysadmin') = 0
    PRINT 'You are not permitted to drop this database'

This works, but I'm wondering if there's a more elegant way to do this using permissions without tweaking each login's own specific permissions?

  • 1
    Since it does not happen very often, I would say the trigger is sufficient. – user507 Oct 16 '12 at 12:42
  • @ShawnMelton - possibly, but I have to spend time trawling the background trace logs pinpointing when they did and then proving it was their finger trouble. It's one less thing I want to have to do. – Kev Oct 16 '12 at 14:10
  • 1
    Why not just have it raise an error, and roll the transaction back. Then you could also just write to a audit table, then you don't have to traverse the trace files. – user507 Oct 16 '12 at 15:30
  • What do you have to change? Are all of your db owners also sysadmin? – Aaron Bertrand Oct 16 '12 at 15:38
  • 1
    So if you use the trigger, why would you have to do anything else with logins or the logs? You could always have the trigger send you an e-mail. – Aaron Bertrand Oct 16 '12 at 16:27

That is your only option.

As a side note, you may also want to check for server role db_creator = 0 as well.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.