Take the 2-minute tour ×
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. It's 100% free, no registration required.

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]
ON ALL SERVER
FOR DROP_database
AS
IF IS_SRVROLEMEMBER('sysadmin') = 0
BEGIN
    PRINT 'You are not permitted to drop this database'
    ROLLBACK
END
GO

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
1  
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
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. –  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
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

1 Answer 1

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

 
discard

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.