Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
Since it does not happen very often, I would say the trigger is sufficient. – Shawn Melton 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
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. – Shawn Melton 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
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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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