Results 1 to 4 of 4

Thread: Table permissions in SPROCS

  1. #1

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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???

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Table permissions in SPROCS

    Quote Originally Posted by techgnome View Post
    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    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
  •  



Click Here to Expand Forum to Full Width