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?