Why does ALTER let me DROP?
Tags: sql, sqlserver
Published: 2020-01-01 14:53:54

---
title: Why does ALTER let me DROP?
published: true
date: 2019-08-28 13:00:19 UTC
tags: SQL, sqlserver
canonical_url: https://am2.co/2019/08/why-does-alter-let-me-drop/
---

[![](http://am2.co/wp-content/uploads/chuttersnap-cGXdjyP6-NU-unsplash.jpg)](https://www.youtube.com/watch?v=m3f0aW_-6gc)  
I was recently chatting with a coworker, discussing why there is no discrete `DROP` permission for tables, procedures, etc. The conversation actually came from a [#SQLHelp](https://sqlcommunity.slack.com/messages/C1MS1RA4B) question on [SQL Slack](http://sqlslack.com) that was asked along the lines of “What if I want to let a user `CREATE` and `ALTER` objects, but not `DROP` them?”

The answer is that if you can `ALTER` an object, then you also have permission to `DROP` it (if you _really_ want to prevent `DROP` commands, you can use a DDL trigger to force a rollback). I’d never thought too much about the question of _why_ permissions are this way–in fact, I often forget that there is no `DROP` permission, and have to re-learn it when I try to grant or deny that permission.

## DROP is destructive

Dropping stuff is destructive. Anyone who’s ever dropped a plate or [a car](https://www.youtube.com/watch?v=SiAbcw5s9_8) knows that. But dropping a table, procedure, or database makes it go away completely. The only way to un-drop something is to recover it from backup, or to re-deploy from source control (you **do** have all your code & schema in source control, right?). Getting back to the original question’s premise (it’s OK to change the object but not to completely make it disappear), I ask…does it even matter? What are you preventing? Is dropping an object worse than altering it?

## ALTER is destructive, too

Altering an object is equally destructive to dropping it. Let us think about the most innocent way in which altering a stored procedure can totally break your code. If you remove a parameter from a stored procedure, and it’s being used by the calling code, it will start to fail with an error:

```sql
CREATE OR ALTER PROCEDURE dbo.Widget_Get 
	@WidgetID INT
AS ...;
GO
EXEC dbo.Widget_Get @WidgetID = 1, @ObsoleteParam = 'Andy';
```

> Msg 8144, Level 16, State 2, Procedure dbo.Widget\_Get, Line 0 [Batch Start Line 0]  
> Procedure or function Widget\_Get has too many arguments specified.

Similarly, adding a new required parameter that _isn’t_ being used by the calling code will also start to fail with an error:

```sql
CREATE OR ALTER PROCEDURE dbo.Widget_Get 
	@WidgetID INT, 
	@Active bit 
AS ...;
GO
EXEC dbo.Widget_Get @WidgetID = 1;
```

> Msg 201, Level 16, State 4, Procedure dbo.Widget\_Get, Line 0 [Batch Start Line 0]  
> Procedure or function ‘Widget\_Get’ expects parameter ‘@Active’, which was not supplied.

But that’s just how innocent mistakes can break stuff.

#### What if someone is being malicious?

Maybe Disgruntled Doug knows that you don’t have that procedure in source control, and wants to destroy the production code for `dbo.Widget_Get` and make it difficult to recover. If he couldn’t drop the procedure, he could still destroy production code…and it would be super easy. Disgruntled Doug can delete all the code without dropping the procedure:

```sql
CREATE OR ALTER PROCEDURE dbo.Widget_Get 
	@WidgetID INT
AS RETURN 0;
GO
EXEC dbo.Widget_Get @WidgetID = 1;
```

> Commands completed successfully.

#### OK, but what about tables?

How badly can you break a table if you could alter, but not drop it? Well, you could truncate it. The permission for `TRUNCATE TABLE` is included in `ALTER TABLE`…so just trash the data if you want to be malicious. But maybe there are foreign key constraints that will make truncating the table difficult. How else could you be destructive?

Let’s look at an example. The `dbo.Person` table is probably pretty central to the database. I could imagine there being dozens, or even hundreds of FKs pointing at `PersonID`, making truncating this table difficult for Disgruntled Doug:

```sql
CREATE TABLE dbo.Person(
     PersonID       int          NOT NULL,
     PersonType	    nchar(2)     NOT NULL,
     Title          nvarchar(8)      NULL,
     FirstName      nvarchar(60) NOT NULL,
     MiddleName     nvarchar(60)     NULL,
     LastName       nvarchar(60) NOT NULL,
     Suffix         nvarchar(10)     NULL,
     ModifiedDate   datetime     NOT NULL,
     CONSTRAINT PK_Person_BusinessEntityID 
          PRIMARY KEY CLUSTERED (PersonID)
);
```

Disgruntled Doug wants to break this _fast_. He’s gotta finish breaking things before HR shows up at his desk to walk him out. Updating columns isn’t going to be fast enough. He can just drop all the columns!

```sql
ALTER TABLE dbo.Person
     DROP COLUMN PersonType, 
                 Title, 
                 FirstName, 
                 MiddleName, 
                 LastName, 
                 Suffix, 
                 ModifiedDate;
```

Now the data is all gone. All that’s left is those pesky `PersonID`s.

### OK, but what about permissions

Dropping an object also drops the permissions. So one could argue that preventing the DROP will at least preserve the permissions—presumably so that you can restore the object definition and not have to restore permissions? To me, this seems like a straw man argument. If you’re restoring the object definition from somewhere (source control? backup?), you can restore permissions from the same place. Permissions are pretty darned important–they should really be kept in source control.

## Summary

Yes, the `DROP` permissions are included when you `GRANT ALTER`. And that’s because someone can intentionally, or unintentionally completely destroy an object without needing to drop it. In a way, dropping is just the most extreme version of altering it.

The post [Why does ALTER let me DROP?](https://am2.co/2019/08/why-does-alter-let-me-drop/) appeared first on [Andy M Mallon - AM²](https://am2.co).