Permissions

  • I am looking for option to truncate table since we don't need the logs of what deleted.

    Below is what I found with minimum level of access, Grant control on table to user. Do you agree or is there a better way?

    https://www.sqlservercentral.com/blogs/sql-server-truncate-table-permissions

  • no. not the best way, neither the minimum access - its more like FULL access.

    grant control on a table is a SUPER SET of permissions that include ALTER (which is enough for the truncate bit)

    CONTROL grants the following permissions on the object and these are WAY too much to grant

    • ALTER
    • CONTROL
    • DELETE
    • INSERT
    • REFERENCES
    • SELECT
    • TAKE OWNERSHIP
    • UPDATE
    • VIEW CHANGE TRACKING
    • VIEW DEFINITION

     

  • Thanks. Can you advise the better option for truncate only table level permission? This particular table will be called in store proc.

  • Just ALTER on the table is the minimum permission for TRUNCATE

  • either ALTER table or execute permissions on a proc that does the truncate like the following

    create procedure TruncateMySchemaTable
    with execute as owner
    as truncate table schemaname.MyTable;
  • Thanks. If execute as clause is used means then the store proc's need to be modified right?

  • the truncate sp needs execute as owner.

    the sp that calls it does not need to have "execute as owner" - only change you need to do on that one is to replace "truncata table" with "exec truncatetablexsp"

     

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

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