patternsqlMinor

Is there a way to prevent a database from being deleted from the SSMS GUI?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

frompreventthessmswaybeingdatabasedeletedtheregui

Problem

Using SQL Server Management Studio, it is possible to right-click a database in the Object Explorer and delete it. (You can also delete a database using T-SQL, not demonstrated here.) I accidentally deleted the wrong database (thankfully, I was able to restore from a recent backup).

Still, it got me thinking. Is there a way to configure a database against accidental deletion -- by GUI or by T-SQL Script? If I actually need to remove a specific database, then I should have to take a few deliberate steps to enable database deletion. Otherwise the database cannot be deleted.

I searched for "prevent database deletion" and all I found were how-to articles about how to prevent table row data from being deleted -- not accidental deletion of the entire database.

Solution

You cannot prevent it directly from the GUI, as far as I know. You can create a Server-scoped trigger to monitor for DROP_DATABASE events and throw an error to the user if invoked.

USE [master]
GO
CREATE DATABASE [CannotDropMe];
GO

CREATE TRIGGER TRG_PreventDropDB ON ALL SERVER
FOR DROP_DATABASE
AS
BEGIN
    RAISERROR('You cannot drop databases on this server.', 16,1);
    ROLLBACK;
END
GO

-- ERROR
DROP DATABASE [CannotDropMe];
GO

-- DB still exists
SELECT * FROM sys.databases WHERE name = 'CannotDropMe';
GO

Also if you want to log the statements (attempted DROP DATABASE) along with any other info you want, you can alter the trigger to something like this.

/* If logging we need a log table */
CREATE TABLE [SomeDB].dbo.[DropDatabaseLog] (Command VARCHAR(100), DBUser NVARCHAR(100), ServerName NVARCHAR(100), EventTime DATETIME DEFAULT(GETDATE()));
GO

CREATE TRIGGER TRG_PreventDropDB ON ALL SERVER
FOR DROP_DATABASE
AS
BEGIN
    DECLARE @CommandText NVARCHAR(MAX);
    SELECT @CommandText = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)');
    ROLLBACK;

    SET NOCOUNT ON;
    INSERT INTO [SomeDB].dbo.[DropDatabaseLog] (Command, DBUser, ServerName)
    SELECT @CommandText,
        CURRENT_USER,
        @@SERVERNAME
    RAISERROR('You cannot drop databases on this server.', 16,1);
END
GO

-- ERROR
DROP DATABASE [CannotDropMe];
GO

SELECT * FROM [DropDatabaseLog];

The database(s) will still exist, and you get some info on potentially who to yell at :)

Edit: I blindly answered this without seeing Shaulinator's answer and links. Check out both links, the first shows how you can filter on specific databases, which may be useful.

Code Snippets

USE [master]
GO
CREATE DATABASE [CannotDropMe];
GO

CREATE TRIGGER TRG_PreventDropDB ON ALL SERVER
FOR DROP_DATABASE
AS
BEGIN
    RAISERROR('You cannot drop databases on this server.', 16,1);
    ROLLBACK;
END
GO

-- ERROR
DROP DATABASE [CannotDropMe];
GO

-- DB still exists
SELECT * FROM sys.databases WHERE name = 'CannotDropMe';
GO

/* If logging we need a log table */
CREATE TABLE [SomeDB].dbo.[DropDatabaseLog] (Command VARCHAR(100), DBUser NVARCHAR(100), ServerName NVARCHAR(100), EventTime DATETIME DEFAULT(GETDATE()));
GO

CREATE TRIGGER TRG_PreventDropDB ON ALL SERVER
FOR DROP_DATABASE
AS
BEGIN
    DECLARE @CommandText NVARCHAR(MAX);
    SELECT @CommandText = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)');
    ROLLBACK;

    SET NOCOUNT ON;
    INSERT INTO [SomeDB].dbo.[DropDatabaseLog] (Command, DBUser, ServerName)
    SELECT @CommandText,
        CURRENT_USER,
        @@SERVERNAME
    RAISERROR('You cannot drop databases on this server.', 16,1);
END
GO

-- ERROR
DROP DATABASE [CannotDropMe];
GO


SELECT * FROM [DropDatabaseLog];

Context

StackExchange Database Administrators Q#170885, answer score: 9

Revisions (0)

No revisions yet.