Post reply

T-SQL: Truncate Table ?!

  • February 19, 2007 at 10:22 am

    #172265

    Hello,

    I've created a stored procedure that gets executed in one my steps in a DTS package.  Everything works fine in development.  FINE!!

    When I moved to QA, my package fails at the stored procedure.  The stored procedure is failing at the line:

    truncate table myControltbl

    I set the permissions for the user as I would any table (select, insert, update and delete).  However, when this line is run in our QA environment,it is saying "user doesn't have permissions to perform function on myControltbl".

    Is there something extra I have to set to execute a truncate statement rather than a delete table?  I thought if I set delete then truncate should work also.  I went into query analyzer on the QA server, logged on as the correct user and issued a delete statement:

    delete from myControltbl

    It worked no problem.  When I typed the truncate statement it did indeed fail.

    If this is the wrong forum I apologize, but I didn't know where to post since I don't know what is the problem with the truncate statement!  It is definitely erroring at the truncate table statement.

    Thanks for your help.

    Tony

     

    Things will work out.  Get back up, change some parameters and recode.

  • February 19, 2007 at 10:26 am

    #690520

    Truncate statements require ALTER TABLE permissions.

  • February 20, 2007 at 12:38 am

    #690578

    in adition to Aaron's reply ..

    New with sql2005 is the "execute as .." for sp's.

    check out "Using EXECUTE AS to Create Custom Permission Sets " in BOL.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • February 20, 2007 at 2:42 am

    #690597

    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.(BOL)

    -------------------------------------------------------------------------
    Normal chaos will be resumed as soon as possible. :crazy:

  • February 21, 2007 at 9:35 am

    #690963

    Thanks everyone.

    Just when you think you've got it down pretty well...you learn something new.  I thought I knew T-SQL pretty well and have used Truncate a lot. 

    I changed my stored procedure to use the DELETE Table statement.  I'm not allowed to change the security permissions at QA.

    Thanks again.

    Tony

     

     

    Things will work out.  Get back up, change some parameters and recode.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply