What permissions are necessary for truncating a table?

I have a SQL account with the following permissions on a database:

The db_executor role you see this account being a member of was created by this script:

CREATE ROLE [db_executor] AUTHORIZATION [dbo]
GO

GRANT EXECUTE TO [db_executor] 
GO

When I run a select, update, insert or delete on the table, it works fine. When I try to truncate the table, it gives me this error message:


Cannot find the object "TableName" because it does not exist or you do not have permissions.

What permission is this account missing?

The best place to look for this information is in books online. The article on TRUNCATE TABLE here indicates:


The minimum permission required is ALTER on table_name. TRUNCATE TABLE
permissions default to the table owner, members of the sysadmin fixed
server role, and the db_owner and db_ddladmin fixed database roles,
and are not transferable. However, you can incorporate the TRUNCATE
TABLE statement within a module, such as a stored procedure, and grant
appropriate permissions to the module using the EXECUTE AS clause.

So ALTER is the minimum permissions required. You can get that as DB Owner, you can get that as DB_DDLAdmin. Or just grant alter.

If you think about what truncate does and how it works, this makes sense, it is a pretty "severe" command and empties the table of data and does it quickly.

StackExchange Database Administrators Q#52828, answer score: 40