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.
