PermissionsThe 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, you have two choices: Grant users ALTER permission OR Create stored procedures (with EXECUTE AS) which will truncate specific tables and give users permission to execute these procs.

Just one more idea: You can create SQL script/proc which will auto generate such stored procedures for the required tables.

Eh, yes, there are concerns, since CONTROL gives them full control on the table. ALTER is slightly better, but that may be bad enough. To you want these users add columns to right and left on these tables?

I think a better solution is to bundle the TRUNCATE TABLE statements in a stored procedure which you sign with a certificate and the create a user from that certificate that you grant the required permissions. Finally grant the users in question permission to execute the procedure(s). For more details, see this article on my web site: http://www.sommarskog.se/grantperm.html

PermissionsThe 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, you have two choices: Grant users ALTER permission OR Create stored procedures (with EXECUTE AS) which will truncate specific tables and give users permission to execute these procs.

Just one more idea: You can create SQL script/proc which will auto generate such stored procedures for the required tables.

+1000 to the stored procedure method.

--Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T."--22 Aug 2013