SQL Truncate table 权限

cnq

对于一些很大的表，如果要删除数据用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