SQL truncate table permission

For some large tables, it is better to use truncate table to delete data than delete data.

Truncate table (TRANSACT-SQL)

Removes all rows from a table without logging the individual row deletions. truncate table is similar to the delete statement with no WHERE clause; however, truncate table is faster and uses fewer system and transaction log resources.

However, truncate table requires a relatively high level of permissions (db_writer permission is not enough), so it is quite contradictory.

Permissions

The minimum permission required is alter onTable_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 induplicate ate the truncate TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the execute as clause.

Therefore, if you want to use truncate table, grant alter permission to user or create a stored procedure, and then execute

Execute as clause (TRANSACT-SQL ): In SQL Server, you can define the execution context of the following user-defined modules: Functions (except for inline table valued functions), processes, queues, and triggers. References: http://msdn.microsoft.com/zh-cn/library/ms188354.aspx