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?
Best Answer
That is your only option.
As a side note, you may also want to check for server role db_creator = 0 as well.