SQL Server Truncate Table Permissions

I saw a note recently where someone asked what permissions were needed for a user to execute TRUNCATE TABLE. In previous versions we needed ownership of the table or DBO level permissions. I had thought this was changed in SQL 2005 to require just the CONTROL permission.

However when I checked the TRUNCATE Books Online page, I found this: 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.

Alter permissions is the minimum?!?!!?

That sounded fishy, so I did this. First I created a new user, with no permissions other than public. My user was, appropriately, MyTestUser.

Next I created a table and granted permissions:

CREATE TABLE TRLC 
(
  est_no varchar(10) default ' '
, right_no int default 0
)
GO

INSERT TRLC SELECT 'Test', 1

GRANT CONTROL ON TRLC TO MyTestUser

I then opened up another Query Window and changed the connection to use MyTestUser. This user only had CONTROL permissions and nothing else. A quick test showed that this user could indeed clear out the table. This:

TRUNCATE TABLE dbo.TRLC

executed without error.

I think Books Online needs an update, and I’ll submit a note to that team to clarify this.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

2 Responses to SQL Server Truncate Table Permissions

  1. Nagi says:

    CONTROL is higher privilege than Alter.

  2. way0utwest says:

    That makes sense, and I’m not sure why I thought ALTER was higher. The permissions documentation isn’t great in terms of explaining this out. I’ve altered my note to ask that they include both items needed in the docs.

Comments are closed.