3

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.

SSMS Object Explorer, about to delete a database

  • Does your login has sysadmin rights? Are they required to do your regular work? – RDFozz Apr 13 '17 at 0:00
2

The suggestion given by Phil prevents all databases in the server from being deleted. What if there's only the need to protect one database on the server from accidental deletion?

Let's imagine this database is named ProductionDatabase. I managed to achieve that with the following code:

CREATE OR ALTER TRIGGER TRG_PreventDropProductionDB ON ALL SERVER
FOR DROP_DATABASE
AS
BEGIN
    IF DB_NAME() = 'ProductionDatabase'
        BEGIN
            RAISERROR('You cannot drop the production database.', 16,1);
            ROLLBACK;
        END
END
GO
| improve this answer | |
8

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.

| improve this answer | |
  • 1
    I like the embedded code + the logging feature, I didn't think of that. I still think having triggers in place and assigning the permissions to have the ability to disable the triggers is a solid approach vs using various accounts to manage this, but I do a lot of DDL / DML in all environments at the current shop, so I suppose the solution is semi-situational. Fantastic answer though! – Shaulinator Apr 13 '17 at 13:49
  • 1
    I like this. You could even go a step further to get closer to the literal request for preventing the action from SSMS by adding the following to the top of the Trigger: IF (NOT EXISTS(SELECT * FROM sys.dm_exec_sessions sess WHERE sess.[session_id] = @@SPID AND sess.[program_name] LIKE N'Microsoft SQL Server Management Studio%')) BEGIN RETURN; END; :-) – Solomon Rutzky Apr 13 '17 at 17:03
3

Yes and no. I don't know how you can prevent this from the GUI only, but you can prevent a person from dropping a database. See link for a trigger that shows how you can do this. If you are a person who adds and drops databases frequently, I would say you could run enable / disable trigger as seen here in the same transaction so you can drop the database but leave the trigger in tact. So this may not be what you wanted, but you can at least prevent accidentally dropping databases.

| improve this answer | |
2

Create a user that doesn't have permission to delete databases. Log in as that user for when you are doing non-administrative things like queries.

| improve this answer | |
  • This looks like the simplest solution. Have my default login have the more dangerous permissions disabled and a special login for the dangerous permissions. – RandomHandle Apr 13 '17 at 5:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.