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 question on SQL Slack that was asked along the lines of “What if I want to let a user
ALTER objects, but not
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.
Dropping stuff is destructive. Anyone who’s ever dropped a plate or a car 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?
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:
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:
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.
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:
CREATE OR ALTER PROCEDURE dbo.Widget_Get @WidgetID INT AS RETURN 0; GO EXEC dbo.Widget_Get @WidgetID = 1;
Commands completed successfully.
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:
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!
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
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.
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.