How to remove drop database permission from db_owner?

Question

Is it possible to remove the DROP DATABASE permission from db_owner? How to achieve this?

Answer 1

No, you can not.

• Except for the public database role, the permissions assigned to the fixed database roles can't be changed.
• Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also DROP the database in SQL Server.

https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver16#fixed-database-roles

If you want to restrict user's behavior, create a user-define db-role, GRANT or DENY permissions on it, and add the user to this role.

Answer 2

as already answered by Roger Kong you can't remove the specific cant drop the database from the db_owner group.

However, what I have been doing in similar situations is to create a server side trigger that does something else and does not allow to drop the database.

Here for instance I don't allow to create a linked server, using a server side trigger. it definitely works.

in this answer here you can see the DDL Statements That Have Server or Database Scope and this list includes the drop database command, so it is possible.

Just as an idea for you to consider, you might be able to track drop database and other commands using extended events. It will require a bit of work to get the functionality you want though.