Preventing Database Deletion in SSMS

Question

prevent accidental database deletions within SQL Server Management Studio (SSMS). Currently, I am exploring options to enforce this restriction effectively

implemented a DDL trigger

Is the above DDL trigger approach appropriate for preventing database deletions in SSMS?

Are there any limitations or scenarios where this trigger might not be effective?

What alternative solutions or best practices do you recommend for enforcing restrictions on database deletions within SSMS?

Answer 1

Hi Niket Kumar Singh ,

I understood that you want ro prevent accidental DB deletation over a SQL Server on Azure VMs. In that case as you have some options as tradditional SQL Server database creating a trigger based on the drop action , here one example:

https://dba.stackexchange.com/questions/170885/is-there-a-way-to-prevent-a-database-from-being-deleted-from-the-ssms-gui

On the other hands, the main limitation on this, the trigger will not prevent a user with sufficient permissions from disabling the trigger and then dropping the database. Additionaly , The trigger only applies to the server where it’s created. If the database is hosted on multiple servers, you’ll need to create the trigger on each server.

The best alternative to prevent the deletation is limiting the permissions of users who don’t need to delete databases. Only grant DROP DATABASE permissions to trusted roles. An example could be create a role with the drop action on any database (https://learn.microsoft.com/en-us/sql/t-sql/statements/create-role-transact-sql?view=sql-server-ver16):

CREATE ROLE NoDropRole;
GRANT SELECT, INSERT, UPDATE, DELETE ON MyTable TO NoDropRole;
ALTER ROLE NoDropRole ADD MEMBER MyUser;

Finally I recommend you to Regularly backup your databases. If a database is accidentally deleted, you can restore it from a backup.

If the information helped address your question, please Accept the answer.

Luis

Answer 2

@Niket Kumar Singh Adding to Luis Arias you can also refer to the blog here https://techcommunity.microsoft.com/t5/azure-database-support-blog/protecting-deletions-of-azure-sql-resources/ba-p/368903 and a similar question from SO forum https://dba.stackexchange.com/questions/323746/how-to-prevent-deletion-of-azure-sql-db-in-ssms

Regards

Geetha