Re: Truncate Table vs. Delete From

From: Jasper Smith (jasper_smith9@hotmail.com)
Date: 08/21/02 

From: "Jasper Smith" <jasper_smith9@hotmail.com>
Date: Wed, 21 Aug 2002 21:49:12 +0100

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

See TRUNCATE TABLE in BOL for details

HTH
Jasper Smith

"Don Reinhard" <reinhard@stjosephs-marshfield.org> wrote in message
news:c5f201c2494a$40b18ae0$39ef2ecf@TKMSFTNGXA08...
> 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: deny truncate?
    ... TRUNCATE TABLE permissions default to the table owner, ... sysadmin fixed server role, and the db_owner and db_ddladmin fixed database ...
    (microsoft.public.sqlserver.programming)
  • Re: deny truncate?
    ... TRUNCATE TABLE permissions default to the table owner, ... sysadmin fixed server role, and the db_owner and db_ddladmin fixed database ...
    (microsoft.public.sqlserver.security)
  • 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)
  • Re: Truncate Table vs. Delete From
    ... permissions (SELECT, INSERT, UPDATE, DELETE, EXECUTE and REFERENCES). ... TRUNCATE TABLE) are different. ...
    (microsoft.public.sqlserver.security)