TRUNCATE Permission: Letting a User Empty One Table

TRUNCATE permission does not exist as a grant you can hand out on its own. The caller needs ALTER on the table, which is far too much. A small procedure can give a loader account that one action and nothing else.

Why DELETE is not enough

Here is a question I get a lot. The nightly loader can delete every row in the staging table, so why can’t it truncate that same table? Because TRUNCATE is treated as a schema change. It needs ALTER on the table.

And ALTER is a big key. It also lets the caller add columns, drop columns and change the table design. A loader account that only wants a clean table does not need any of that. Handing it ALTER to save a few minutes of scripting is how a staging table ends up with a surprise column.

The demo makes a staging table with two rows. The user StageLoader has no login, which is enough for testing permissions. It can read, insert and delete, but nothing more.

DROP TABLE IF EXISTS dbo.LoadStage;
CREATE TABLE dbo.LoadStage (Id int IDENTITY PRIMARY KEY, ValueText varchar(20));
INSERT dbo.LoadStage (ValueText) VALUES ('first'), ('second');
GO
IF USER_ID(N'StageLoader') IS NOT NULL DROP USER StageLoader;
CREATE USER StageLoader WITHOUT LOGIN;
GRANT SELECT, INSERT, DELETE ON dbo.LoadStage TO StageLoader;

Now let StageLoader try a plain TRUNCATE. It fails with error 1088. The message says the object cannot be found or you lack permissions, which is SQL Server’s polite way of saying no.

EXECUTE AS USER = N'StageLoader';
BEGIN TRY
    TRUNCATE TABLE dbo.LoadStage;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS error_number, ERROR_MESSAGE() AS error_message;
END CATCH;
REVERT;

Wrap the one action in a procedure

The fix is a procedure that does exactly one thing. WITH EXECUTE AS OWNER means it runs with the owner’s rights, not the caller’s. The owner has ALTER, so the TRUNCATE works.

Notice the table name is typed right into the procedure. There is no parameter for it. If you let callers pass a table name, you have built a tool that empties any table they can name.

CREATE OR ALTER PROCEDURE dbo.EmptyLoadStage
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;
    TRUNCATE TABLE dbo.LoadStage;
END;
GO
GRANT EXECUTE ON dbo.EmptyLoadStage TO StageLoader;

Check what the caller can really do

Run the next block as StageLoader. The first result says CanAlterTable is 0, so the caller still has no ALTER on the table. The procedure runs anyway. The second result says StageRows is 0, so the table is empty.

Then I insert one new row. Before the truncate the table held ids 1 and 2, so a plain insert would have got 3. The new row gets Id 1, because TRUNCATE also resets the identity counter.

EXECUTE AS USER = N'StageLoader';
SELECT HAS_PERMS_BY_NAME(N'dbo.LoadStage', N'OBJECT', N'ALTER') AS CanAlterTable;
EXEC dbo.EmptyLoadStage;
REVERT;

SELECT COUNT(*) AS StageRows FROM dbo.LoadStage;

INSERT dbo.LoadStage (ValueText) VALUES ('after truncate');
SELECT Id FROM dbo.LoadStage;

Foreign keys still say no

The procedure does not bypass every rule. If another table has a foreign key pointing at the staging table, TRUNCATE fails with error 4712, even for the owner. Here a small table named LoadStageNote points at LoadStage, and the caller gets that error.

Please do not drop the foreign key just to make the cleanup work. That key is protecting something. Either delete from the child table first, or use DELETE for this table.

DROP TABLE IF EXISTS dbo.LoadStageNote;
CREATE TABLE dbo.LoadStageNote (NoteId int PRIMARY KEY,
                                StageId int NOT NULL REFERENCES dbo.LoadStage (Id));

EXECUTE AS USER = N'StageLoader';
BEGIN TRY
    EXEC dbo.EmptyLoadStage;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS error_number, ERROR_MESSAGE() AS error_message;
END CATCH;
REVERT;

Keep the procedure narrow

This design is only as safe as the procedure stays small. Anyone who can alter it can make it do more, and it will run with the owner’s rights. So keep the list of people who can change it short. If you later edit it, test the same caller again.

Try this on your own server with a test table first. Run it as a login that has only EXECUTE, and confirm that HAS_PERMS_BY_NAME says 0 for ALTER. The last block removes everything the demo created.

DROP TABLE IF EXISTS dbo.LoadStageNote;
DROP PROCEDURE IF EXISTS dbo.EmptyLoadStage;
DROP USER IF EXISTS StageLoader;
DROP TABLE IF EXISTS dbo.LoadStage;

Give your loader the one action it needs, and keep the keys to the table in your own pocket.

A cleanup right is not table ownership, it is one action you chose to expose.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.