Permissions required to Truncate

  • Hi,

    What is the least privileged role that a user needs to truncate a table. The delete permission does not seem to have a control over truncate command. I need to allow the batch operator to be able to truncate the staging tables. This opertator should have the minimum pernmissions. This is for DTS on SQL 2K

    Thanks!

  • I think the minimum you need is for your operators to be a member of db_ddladmin however this role probably has too many priveliges for your liking. You could put the staging table in a seperate db where the operators can have access.

  • Truncate permission also defaults to the table owner.  If the operator were the owner of the staging tables he or she could truncate them but not others in the database.

    Greg

    Greg

  • I would think it might be a better idea to create a stored procedure to truncate, and give the operator execute permission on the SP.

    Dick

  • Dick, I don't think that will work any more with service pack 3 - you can't grant permission to truncate from within an sp.

  • 'truncate' is a DDL command and has never been 'grant-able'. ddl-admin role will work since 'truncate' falls into that category.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • To allow truncate you can add 'WITH EXECUTE AS OWNER' to the procedure.

    😉

    Joerg

  • jnaumann (12/3/2008)


    To allow truncate you can add 'WITH EXECUTE AS OWNER' to the procedure.

    😉

    Joerg

    ...but only in SQL 2005.

    Greg

  • Hi,

    The user need to be member of db_ddladmin at least.

    http://msdn.microsoft.com/en-us/library/aa260621(SQL.80).aspx

    Regards

  • thanks everyone 🙂

    I had posted the question in 2006, and have got three replies in the last two days!! Appreciate your help.

  • If you give user CONTROL permission on that table( if its just some table in SP scope) than it should be enough.

  • If you give user CONTROL permission on that table( if its just some table in SP scope) than it should be enough.

Viewing 12 posts - 1 through 11 (of 11 total)

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