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.

Filed under: Blog Tagged: security, syndicated, T-SQL