46

What permission do I need to GRANT a user, in MSSQL, in order to be able to truncate a table?

I'm trying to grant the minimal set of permissions, but I can't use DELETE, because the table is very large, and I want the operation to be quick.

Shahar Mosek
  • 1,922
  • 3
  • 17
  • 27

5 Answers5

76

You need the ALTER permission: see the Permissions section here.

Note that you can also use a stored procedure with EXECUTE AS, so that the user running the stored procedure does not need to even be granted the ALTER permission.

Pittogatto
  • 43
  • 1
  • 9
Shahar Mosek
  • 1,922
  • 3
  • 17
  • 27
  • 2
    Not only was this answer correct, but did the "teach a man to fish" by pointing to the documentation so such answers can be found by the OP in the future. Why the downvote? – Ben Thul Jan 19 '11 at 13:36
  • 2
    @Ben - I suspect because the OP posted this answer about a nanosecond after posting the question. I didn't downvote though as the FAQ allows this. – Martin Smith Jan 19 '11 at 14:25
  • @Martin - it wasn't a nanosecond. It was 2 minutes - that's how long it took me to find the answer. – Shahar Mosek Jan 26 '11 at 14:26
  • 1
    tldr: grant alter on to
    – fiat Jun 13 '14 at 06:23
  • Granting ALTER permissions can be quite dangerous, so take care (read the CAUTION box): https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-schema-permissions-transact-sql?view=sql-server-ver16 – mzuther Jun 06 '23 at 10:39
9

You can create a stored procedure with execute as owner:

create procedure dbo.TruncTable
with execute as owner
as
truncate table TheTable
go

Then grant execution permissions to whoever needs to truncate that table:

grant execute on TruncTable to TheUser

Now TheUser can truncate the table like:

exec dbo.TruncTable
Andomar
  • 232,371
  • 49
  • 380
  • 404
8

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. For more information, see Using EXECUTE AS to Create Custom Permission Sets.

Source

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
1

Don't GRANT, hide...

CREATE TRIGGER TRG_MyTable_Foo 
WITH EXECUTE AS OWNER
INSTEAD OF DELETE
AS
IF CONTEXT_INFO() = 0x9999
BEGIN
    TRUNCATE TABLE MyTable
    SET CONTEXT_INFO 0x00
END
GO

SET CONTEXT_INFO 0x9999
DELETE MyTable WHERE 1=0

SET CONTEXT_INFO may be is without any doubt better to separate a normal DELETE from a TRUNCATE TABLE

I haven't tried this...

Edit: changed to use SET CONTEXT_INFO.

gbn
  • 422,506
  • 82
  • 585
  • 676
  • +1 I would have said `SET CONTEXT_INFO` would be a must though to avoid causing unpleasant surprise! – Martin Smith Jan 19 '11 at 12:20
  • 1
    -1 This would make a delete which hits zero rows truncate the table. You're usually a great answerer, but this is an unmaintainable hack imho. – Andomar Jan 19 '11 at 12:31
  • @Andomar: yes, corrected. I neither thought it through fully nor updated my answer quickly enough – gbn Jan 19 '11 at 17:30
-2

You can create a stored procedure with execute as owner to only one table or a store procedure to any table:

CREATE PROCEDURE [dbo].[spTruncate]
    @nameTable varchar(60)  
    WITH EXECUTE AS OWNER
    AS

SET NOCOUNT OFF;
DECLARE @QUERY NVARCHAR(200);

SET @QUERY = N'TRUNCATE TABLE ' + @nameTable + ';'

EXECUTE sp_executesql @QUERY;
David Gorsline
  • 4,933
  • 12
  • 31
  • 36
user3854427
  • 13
  • 1
  • @Graeme, why is this dangerous? Both you and the other commenter think it is but I'm not sure why. – FoxDeploy Apr 06 '18 at 20:19
  • 2
    Hi, @FoxDeploy-- If some malicious user uses SQL Injection, such as this-- `DECLARE @nameTable VARCHAR(60) = 'SomeTable; DROP DATABASE YourDatabase; --'; EXECUTE dbo.spTruncate @nameTable; GO` More here: https://security.stackexchange.com/questions/128412/sql-injection-is-17-years-old-why-is-it-still-around – Graeme Aug 13 '18 at 19:07