Re: Users and Truncate Table

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 08/29/05 

Date: Mon, 29 Aug 2005 17:16:51 -0400

In SQL2000 you need to have dbo rights to truncate a table. 2005 makes this
much easier but now you are stuck with giving them enough permissions to do
more than what you want if you let them Truncate. If the table is small
just do a Delete with no WHERE clause. 

-- 
Andrew J. Kelly  SQL MVP
"Ben" <ben_1_ AT hotmail DOT com> wrote in message 
news:0BE17F50-9BCC-41DD-AE86-E076349AB048@microsoft.com...
> How can i set up a use that is able to run a stored procedure that 
> truncates
> a specific table?  I could swap the truncate for a drop table and create
> table statement if that works better.  The tables the user will be 
> truncating
> only contain summaries of the real data so i believe there isnt a worry of
> security
>
> thanks for the help!
> ben

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)
  • Re: Truncate Table vs. Delete From
    ... permissions (SELECT, INSERT, UPDATE, DELETE, EXECUTE and REFERENCES). ... TRUNCATE TABLE) are different. ...
    (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)