Truncate Table vs. Delete From

From: Don Reinhard (reinhard@stjosephs-marshfield.org)
Date: 08/21/02 

From: "Don Reinhard" <reinhard@stjosephs-marshfield.org>
Date: Wed, 21 Aug 2002 12:37:58 -0700

In the books on-line, I found the following excerpt on
this subject:

If you want to delete all the rows in a table, TRUNCATE
TABLE is faster than DELETE. DELETE physically removes
rows one at a time and records each deleted row in the
transaction log. TRUNCATE TABLE deallocates all pages
associated with the table. For this reason, TRUNCATE TABLE
is faster and requires less transaction log space than
DELETE. TRUNCATE TABLE is functionally equivalent to
DELETE with no WHERE clause, but TRUNCATE TABLE cannot be
used with tables referenced by foreign keys. Both DELETE
and TRUNCATE TABLE make the space occupied by the deleted
rows available for the storage of new data.

Permissions:
Delete permissions default to members of the sysadmin
fixed server role, the db_owner and db_datawriter fixed
database roles, and the table owner. Members of the
sysadmin, db_owner, and the db_securityadmin roles, and
the table owner can transfer permissions to other users.
SELECT permissions are also required if the statement
contains a WHERE clause.

With a recent experience, there is more to this than meets
the eye. Using the premise that having execute
permissions on a Stored Procedure overrides any
permissions set on a table used within a SP, I did some
more security modeling/testing.

Here's what I did. I used TestAcct as the guinea pig
account. I setup a user-defined role. TestAcct was
assigned to that role. The role had execute permissions
on a Stored Procedure.

TestAcct executed the Stored Procedure which issued a
Truncate Table command. The procedure failed because of
lack of permissions to perform the command on the table.

I changed the Truncate Table command to Delete From and it
worked. The table involved does not have any foreign keys.

I was wondering if anyone knows the explanation for this
apparent difference between Truncate Table and Delete From.
Thanks,
Don

Relevant Pages

  • Re: Truncate Table vs. Delete From
    ... I think that TRUNCATE is a non-logged operation, ... > Delete permissions default to members of the sysadmin ... > permissions on a Stored Procedure overrides any ... > lack of permissions to perform the command on the table. ...
    (microsoft.public.sqlserver.security)
  • Re: Truncate Table vs. Delete From
    ... TRUNCATE TABLE permissions default to the table owner, ... of the sysadmin fixed server role, ...
    (microsoft.public.sqlserver.security)
  • RE: truncate table
    ... this gives you the option to create a stored procedure with a truncate table statement. ... You can then GRANT the stored procedure to be EXECUTED AS a login or role. ... >>Truncate table permissions are NOT transferable. ...
    (microsoft.public.sqlserver.server)
  • Permissions for executing a stored procedure
    ... Truncate table permissions only default to table owner, ... So, eventhough, you have granted execute permissions to ... Instead using TRUNCATE TABLE, you could use DELETE FROM, ... >We have a stored procedure that has code in it to ...
    (microsoft.public.sqlserver.security)
  • Re: With in a SP Truncate dbo.table table-name permissions..
    ... TRUNCATE TABLE permissions default to the table owner, ... According to sql server ... If a user who creates a stored procedure does ...
    (microsoft.public.sqlserver.security)