What permissions are necessary for truncating a table?
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.
As per this reference in BOL:
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.
You can create a stored procedure with execute as owner to only one table or a stored procedure to any table. In the next code is stored procedure to truncate any table without assinging permission of db_owner
or other:
USE [database name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Yimy Orley Asprilla
-- Create date: Julio 16 de 2014
-- Description: Función para hacer TRUNCATE a una tabla.
-- =============================================
ALTER PROCEDURE [dbo].[spTruncate]
@nameTable varchar(60)
WITH EXECUTE AS OWNER
AS
SET NOCOUNT OFF;
DECLARE @QUERY NVARCHAR(200);
SET @QUERY = N'TRUNCATE TABLE ' + @nameTable + ';'
EXECUTE sp_executesql @QUERY;