Error occurring on truncate table within stored procedure

Question

I have a very strange situation that I have tried to research the answer from the Net to no avail. I am using SQL Server 2008 R2.

I have created a stored procedure that has a TRUNCATE TABLE statement on it amongst other T-SQL statements.

When this is run from SQL Server Management Studio, the stored procedure runs without error.

When run from a Windows Forms application (written in VB.Net with .NET Framework 4), I am getting the error

Cannot find the object tbl_Test1 because it does not exist or you do not have permissions

The connection to the database is set within the application correctly, and what is even stranger is that I have other stored procedures created in exactly the same way, with their own TRUNCATE TABLE statements and these still run without error.

There is execute permission on the stored procedure to the User_Role, of which the calling application is logging on as.

The table tbl_Test1 DOES exist.

I have tried several things and from doing this, have become even more confused about this whole situation.

1. If I put a Select * from tbl_Test1 prior to the TRUNCATE, then the stored procedure works.

2. Because I am returning a select later on, I would prefer this not be present. So I recoded and did a select @Count = count(*) from tbl_Test1 before the TRUNCATE statement, and this fails with the same error above stating the tbl_Test1 doesn't exist or no permission.

I am at a complete loss as to why this might occur. I have several other stored procedures that have truncate statements within them, created in exactly the same way as this one and those work fine when called from the application.

Can anyone help or shed some light on my problem.

Many thanks in advance

Answer 1

Ownership chaining doesn't apply to TRUNCATE TABLE.

You need to grant that permission explicitly (the minimum permission required is ALTER on table_name) or use EXECUTE AS in the stored procedure. Giving EXEC permissions on the stored procedure is not enough.