Preventing Database Deletion in SSMS

Question

Preventing Database Deletion in SSMS

Niket Kumar Singh 830

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

implemented a DDL trigger
User's image

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?

Locked Question. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

Answer accepted by question author

2 additional answers

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://free.blessedness.top/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

Niket Kumar Singh 830 Reputation points

2024-06-28T18:01:21.71+00:00

Hi @Luis Arias

Thank you for the detailed suggestion, appreciate your effort.

Is there any other solution, or the way we can achive the same.

The DB is in the SQL VM.

Answer 2

GeethaThatipatri-MSFT 29,582 Microsoft Employee Moderator

@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

Niket Kumar Singh 830 Reputation points

2024-06-28T17:58:00.0366667+00:00

Hi @GeethaThatipatri-MSFT

Thank you for your suggestion.

As far as i understand this will protect the deletion rom azure portal, not on the DB.

Moreover the DB is in the SQL VM not on the AzureSQL DB.

Is therea any fesiblity we can implement.

Answer 3

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Share via

Preventing Database Deletion in SSMS

2 additional answers