Table permissions in SPROCS

I have a SPROC that basically looks like this:

Code:

---

CREATE PROCEDURE rptPensionBOT
        ,@Start_Effective_Date datetime
        ,@End_Effective_Date datetime
AS

Set NoCount On
Declare @IdList Table (MasId int, MemId int, RateAmt money)
Insert into @IdList Select MasId,MasId,0 From Pension_T Where EffDate between @Start_Effective_Date and @End_Effective_Date
.
.
.
Truncate Table rptPensionBOT_IDList_T
Insert into rptPensionBOT_IDList_T Select * From @IdList
--GRANT CONTROL ON rptPensionBOT_IDList_T TO FundsUser

Select        MA.MasName + Case When MA.Affil<>'1' Then ' (QDRO)' Else '' End                                -- 0
        ,Convert(Char(10),PA.EffDate,101)                                                                -- 1
        ,PT.PenTypeDesc                                                                                -- 2

---

The TRUNCATE and INSERT lines were recently added.

We grant EXEC permissions to the SPROC like this: GRANT EXECUTE ON rptPensionBOT TO FundsUser

Normally this would give full access to ALL OTHER objects within the DATABASE without actually granting permissions.

So I add this new table - and the TRUNCATE and INSERT statements and all of a sudden the end user is getting:

"Cannot find the object "rptPensionBOT_IdList_T" because it does not exist or you do not have permissions".

By running that commented out GRANT CONTROL statement the user got past the error.

What am I missing here???

I believe granting execute gets your basic CRUD, but TRUNCATE is more of an admin instruction, so it requires explicit granting rather than implied. I'm not 100% sure though. I've never used TRUNCATE in production code before.

-tg

Quote:

---

Originally Posted by techgnome

I believe granting execute gets your basic CRUD, but TRUNCATE is more of an admin instruction, so it requires explicit granting rather than implied. I'm not 100% sure though. I've never used TRUNCATE in production code before.

-tg

---

That sounds oddly familiar...

I'll change it to a DELETE instead of TRUNCATE to see if that works better.

This is from the MSDN documentation for the TRUNCATE TABLE statement:

Quote:

---

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.

---