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?