Preventing Database Deletion in SSMS

Niket Kumar Singh 300 Reputation points
2024-06-28T13:20:16.26+00:00

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?

SQL Server on Azure Virtual Machines
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,324 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

Accepted answer
  1. Luis Arias 5,981 Reputation points
    2024-06-28T15:54:57.5466667+00:00

    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


1 additional answer

Sort by: Most helpful
  1. GeethaThatipatri-MSFT 29,017 Reputation points Microsoft Employee
    2024-06-28T17:46:17.41+00:00