5

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.

  • Even truncate would also not free up space as such. The best thing is it is very fast. You have to run dbcc shrinkfile to free space( although whole point is not related to acctual question) – Shanky Feb 24 '15 at 7:57
  • Truncating a heap will free up the space better than just deleting the rows. – Rob Farley Feb 24 '15 at 8:58
8

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.

  • Thanks Remus for the explanation and a new code to try. I was able to create this procedure successfully, but still can't use it to truncate the required table when I try it from an SQL user which don't have rights to "Alter table". I went over with the Module signing link you shared. Didn't really understood much of it. But I guess the DBA would need to run a command givings rights to the particular SQL user which can then run the procedure. I am guessing following command: GRANT EXECUTE ON [dbo].[usp_sysprocesses] TO SysProcRole Will post what happens next. – Vyom Feb 25 '15 at 11:09
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.

  • 1
    Or a rebuild - which can restrict your data movement operations to that table instead of shrinking the file (and likely fragmenting all the other data). – Aaron Bertrand Feb 24 '15 at 21:43
  • Thanks stacylaray for your answer. The way you have answered my questions individually, was very helpful in understanding the concepts. – Vyom Feb 25 '15 at 11:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.