Truncating table without using Alter Table permission

Question

So my DBA doesn't want developers to give Alter table permission. Now one of the procedures that we created requires to truncate tables (since apparently delete only removes records, but truncate frees up space too).

Now in order to use truncate minimum permission required is "Alter table". (Source)

But there was also a solution described to "create a stored procedure with execute as owner to only one table or a stored procedure to any table" (given by user3854427).

My question is in response to the code he provided. Embedding the code for easy reference:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Yimy Orley Asprilla
-- Create date: Julio 16 de 2014
-- Description: Función para hacer TRUNCATE a una tabla.
-- =============================================
ALTER 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;

My questions are:

1. Is the above code creating a stored procedure "on behalf of" OWNER?
2. Who has to create this procedure (DBA?) since I definitely can't create it.
3. Once the DBA create this procedure any member can use it to truncate the table even if they don't have the Alter table permission?

Please help.

Answer 1

No sane DBA will ever allow such a procedure. This is a SQL injection privilege escalation vector. I can pass in the tablename 'x; exec sp_myfoo;' and voila.

There are basic issue:

• table names are NVARCHAR, not VARCHAR
• table names are length 128, not 60
• sysname is a handy type to represent object names, is an alias for NVARCHAR(128)
• tables are qualified by schema and name, not by name only
• procedures that build dynamic SQL must properly quote the names, using QUOTENAME

Lets try again:

 CREATEPROCEDURE [dbo].[spTruncate]
    @schemaName sysname,
    @tableName sysname
 WITH EXECUTE AS CALLER
 AS
 SET NOCOUNT OFF;
 DECLARE @QUERY NVARCHAR(max);
 SET @QUERY = N'TRUNCATE TABLE ' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName );
 EXECUTE sp_executesql @QUERY;

No need to use execute as OWNER. As CALLER is fine, as long as the procedure is properly signed. Ask you DBA to sign the procedure following the standard procedure. See Module Signing and Signing Stored Procedures in SQL Server. Your DBA should know how to do this.

Answer 2

1. No, it is executing TRUNCATE TABLE in the context of the current owner of the module. So, as long as the owner of stored procedure has the ALTER TABLE permission, it will work.
2. Anyone who has CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created, can create the procedure.
3. Any member who has permission to execute the module itself can use it to truncate the table even if they don't have the ALTER TABLE permission.

The difference between DELETE and TRUNCATE is that DELETE logs each row deletion, whereas, TRUNCATE deallocates the data pages and only logs the page deallocations. DELETE requires DELETE permissions on the table and TRUNCATE requires ALTER TABLE.

In order to actually free the space back to the operating system, regardless of DELETE or TRUNCATE, a SHRINKFILE must be performed.