text: Q:
  
  Truncating table without using Alter Table permission
  
  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:
  
  Is the above code creating a stored procedure "on behalf of" OWNER?
  Who has to create this procedure (DBA?) since I definitely can't create it.
  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.
  
  A:
  
  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.
  
  