SQL Truncate table 許可權

對於一些很大的表，如果要刪除資料用Truncate table比Delete效果要好。

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.

但是Truncate table需要的許可權比較高（db_writer許可權是不夠的），所以比較矛盾。

Permissions

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.

所以如果想用Truncate table可以grant alter permission to user或者建立一個預存程序，然後用EXECUTE AS

EXECUTE AS 子句 (Transact-SQL)：在 SQL Server 中，可以定義以下使用者定義模組的執行內容：函數（內聯資料表值函式除外）、過程、隊列和觸發器。References:http://msdn.microsoft.com/zh-cn/library/ms188354.aspx