CODEWITHSUNDEEP
sql-server
AftNow
AftNow

Reputation: 15

Why does the TRUNCATE statement require the ALTER permission?

I need for a certain user to be able to empty a large table, without logging cluttering up the drive. TRUNCATE is perfect for this. The user will have to have the ALTER permission, however, to be able to use TRUNCATE.

Any idea why TRUNCATE would require ALTER? Seems like the DELETE permission should be enough?

Upvotes: 0

Views: 1411

Answers (3)

Piotr Palka
Piotr Palka

Reputation: 3169

TRUNCATE will allow you to avoid execution of the delete triggers.
DELETE permission is not enough to delete rows without proper execution of triggers. I would say that was the reason why Microsoft decided to require more permissions for TRUNCATE than DELETE.

Why do you need additional privileges for truncate table compared to delete? by SQLDenis

When a TRUNCATE occurs, the operation does not log individual row deletions, as DELETE operation does. The reason this is important is because if you have a trigger on the table, in needs to be disabled before the TRUNCATE occurs. Now you know why ALTER TABLE is required, triggers need to be disabled.

Upvotes: 0

Eric Brandt
Eric Brandt

Reputation: 8101

Because TRUNCATE TABLE doesn't operate on the data, it operates on the structure of the table.

When you truncate a table, the data stays exactly where it was, physically, on the disk. Entirely intact. But the SQL Engine ALTERs the structure of the table to point to a new part of the disk where there isn't any data stored yet. That's why the operation is so much faster than a delete, where the data has to be "destroyed" within the structure where it's stored. There's I\O with DELETE, but TRUNCATE is a structural metadata operation.

Edit: Adding to the answer because of some good points in the comments.

To @alroc's point, if there is an IDENTITY column on the table, TRUNCATE will reseed, whereas DELETE will let the value continue to run up.

To @Bohemian's point, I should have prefaced this with "Probably..." because, really, I'm just guessing at Microsoft's logic, and I should have finished with the observation that, whatever the actual reasoning, I think it was a questionable move to require such a high permission level just to clear out a table.

Upvotes: 2

Bohemian
Bohemian

Reputation: 425198

The reason you need ALTER permission is that zapping all data from a table is not an action you would ever need to do as a “normal” database base-using person/app.

Neither is adding columns or indexes etc. These kinds of actions are an operational concern and appropriately require an operational level of authority.

Upvotes: 0

Related Questions