Re: Truncate Table vs. Delete From

From: Dan Guzman (danguzman@nospam-earthlink.net)
Date: 08/22/02 

From: "Dan Guzman" <danguzman@nospam-earthlink.net>
Date: Wed, 21 Aug 2002 21:50:36 -0500

To expand on Jasper's response, ownership chains apply only to object
permissions (SELECT, INSERT, UPDATE, DELETE, EXECUTE and REFERENCES). A
user doesn't need permissions on referenced objects as long as the
ownership chain is unbroken.

Statement permissions (e.g. CREATE TABLE) and implied permissions (e.g.
TRUNCATE TABLE) are different. See the SQL Server 2000 Books Online
(adminsql.chm::/ad_security_94dv.htm) for details.

Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"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: Truncate Table vs. Delete From
    ... TRUNCATE TABLE permissions default to the table owner, ... of the sysadmin fixed server role, ...
    (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)
  • Truncate Table vs. Delete From
    ... If you want to delete all the rows in a table, TRUNCATE ... Delete permissions default to members of the sysadmin ... permissions on a Stored Procedure overrides any ... Truncate Table command. ...
    (microsoft.public.sqlserver.security)
  • Solaris 10 autofs directory permissions - Solution
    ... the fact that my map file has 755 permissions not 644. ... If the execute permission is set, it becomes an executable map which is ... map is expected to return the content of an automounter map ...
    (SunManagers)