Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2001
    Posts
    36

    Unanswered: Grant Premission

    How do I grant permission to another user in a database to truncate a table which is owned by me.

    By default only the owner of the table, DBO, SYSADMIN can truncate any table.

    Is there any other alternat way that I can look for doing it ?

    Thanks

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Like you've stated, the permiisions on TRUNCATE are;

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

    Two alternatives:
    [list=1][*]GRANT DELETE permission on the table to the user and then issue DELETE statement[*]Create a stored procedure that does the TRUNCATE and then GRANT EXECUTE on the stored procedure to the user[/list=1]
    MCDBA

  3. #3
    Join Date
    Nov 2001
    Posts
    36
    Thanks man.
    The second option was preferable.

    Actually I tried to create a procs using the users id and while he had the ddl_admin role granted, then till he had that role he was able to truncate it, but once i revoked the role from that user, he was not able to truncate.

    Thanks for your help man.

  4. #4
    Join Date
    Nov 2001
    Posts
    36
    Some how after granting the execute permission to the proc, the user is still not able to tryncate the table.

    It still gives the permission denied error.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •