Post reply

Grant permissions to truncate one table in a db with out proving DDL_Admin rights

  • February 11, 2016 at 6:05 am

    #308449

    How do I provide permissions to truncate one table in a db without giving DDL_Admin rights?

  • February 11, 2016 at 6:13 am

    #1857991

    Basically, you don't.

    From Books Online

    "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."

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 11, 2016 at 6:41 am

    #1858000

    Thanks Gail!

  • February 11, 2016 at 9:22 am

    #1858067

    SQL!$@w$0ME (2/11/2016)

    How do I provide permissions to truncate one table in a db without giving DDL_Admin rights?

    To provide permissions to a user to truncate a single table you need to grant them ALTER at the table level. Granting at the schema level obviously has a wider impact, you do not need to grant DDL_Admin database role.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • February 11, 2016 at 12:01 pm

    #1858141

    Thanks Perry! That worked.

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

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