-
Oct 17th, 2017, 02:25 PM
#1
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???
-
Oct 17th, 2017, 02:38 PM
#2
Re: Table permissions in SPROCS
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
-
Oct 17th, 2017, 06:39 PM
#3
Re: Table permissions in SPROCS
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.
-
Oct 17th, 2017, 11:26 PM
#4
Re: Table permissions in SPROCS
This is from the MSDN documentation for the TRUNCATE TABLE statement:
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|