• Eugene Elutin (7/19/2013)


    BoL clearly explains what can be done:

    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.

    So, you have two choices:

    Grant users ALTER permission OR Create stored procedures (with EXECUTE AS) which will truncate specific tables and give users permission to execute these procs.

    Just one more idea: You can create SQL script/proc which will auto generate such stored procedures for the required tables.

    +1000 to the stored procedure method.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)