As far as permissions are concerned, I believe one of the advantages of using stored procedures is that anyone who is granted execute permission on them is automatically allowed to execute whatever commands are contained within the sp -- but only through the stored procedure. So, this seems to be a great way to allow something like this without granting them permission to do so. I'm not sure I like the idea of letting them pass in any table name, though. I'd likely either create a specific TRUNC sp for each table I want the user to be able to truncate or write some code that only does the TRUNCATE if specific table names are passed in.

To make the SP work, you need to do something like:

CREATE PROCEDURE dbo.procZAP @tablename varchar(255)=''
AS
EXEC ( 'TRUNCATE TABLE ' + @tablename )
GO

T-SQL won't let you use a variable/parameter just anywhere within a statement. You can get around that using the EXEC[UTE] syntax and also the sp_executesql system proc. The T-SQL help file has explanations and examples of using both. In fact, one of the examples for EXEC shows how to do a parameterized DROP TABLE, very similar to what you are wanting to do here.

Kelly

>>What if you were to create a stored procedure that executes the TRUNCATE TABLE and then give exec priveleges on the SP to that user?
>
>
>Based on BOb's point, this probably would not have worked. I wanted to try it as an excercise but could not get SQL Server to accept the syntax. Here's what I attempted...
>
>

>
>CREATE PROCEDURE dbo.procZAP
>			@tTableName varchar(255)=NULL
>AS
>
>TRUNCATE TABLE @tTableName
>
>GO
>
>

>
>It doesn't seem to like the TRUNCATE TABLE line, although based on my reading of the help and other books, this should be correct. Do you see my error?