none
Can grant truncate table permission to a user? RRS feed

  • Question




  • Whether can grant  truncate table permission to a user separately.

    I know that the role db_ddladmin can do,but the create,alter and drop permissions will be granted the same time(These permissions are not what we want to

    grant to user).

    Is there any solution?

    Best regards!!


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    Wednesday, July 29, 2009 1:05 AM

Answers

All replies

  • You can't grant truncate because for whatever reason, that permission doesn't exist.  What you can do is use a stored procedure and use EXECUTE AS OWNER to get around it.

    create database foo
    go
    create login foobar with password = 'alkdsfji9eorngv';
    go
    use foo
    go
    create user foobar from login foobar;
    go
    create table test(rowid int identity)
    go
    insert into test default values;
    go
    select * from test
    go
    create procedure dbo.truncate_test
    with execute as owner
    as
    truncate table test
    go
    grant execute on dbo.truncate_test to foobar
    go
    execute as login='foobar'
    execute dbo.truncate_test
    revert
    go
    select * from test
    go
    use master
    go
    
    drop database foo
    drop login foobar
    This keeps your database safe and allows you to grant execute on the truncate procedure to allow them to truncate the table.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Wednesday, July 29, 2009 3:45 AM
    Moderator



  • But it came out an error 'incorrect syntax near the keyword execute'


    create procedure dbo.truncate_test
    with execute as owner
    as
    truncate table test
    go



    can you check it?

    TKS
    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    Thursday, July 30, 2009 12:41 AM
  • What version of SQL Server is this for?  If it is 2000 or below, then forget what I wrote above as it is for 2005 or 2008.  If you are on SQL 2005 or 2008 then check the database compatibility level and make sure it is set to at least 90.

    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Thursday, July 30, 2009 12:54 AM
    Moderator



  • What version of SQL Server is this for?  If it is 2000 or below, then forget what I wrote above as it is for 2005 or 2008.  If you are on SQL 2005 or 2008 then check the database compatibility level and make sure it is set to at least 90.

    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!



    Thank you all the same,Jonathan.

    I forgot to say that the circumstance is SQL Server 2000.

    Is there any other way to resolve the problem in SQL Server 2000.

    Best Regards!!


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    Thursday, July 30, 2009 1:38 AM
  • Unfortunately none that I know of. 
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Thursday, July 30, 2009 1:44 AM
    Moderator


  • Unfortunately none that I know of. 
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!


    TKS all the same!!


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    Thursday, July 30, 2009 2:14 AM
  • You can create a stored procedure with execute as owner to only one table or a store procedure to any table. In the next code is  SP to truncate any table without assing permission of db_owner or other:

    USE [database name]
    GO
    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.
    -- =============================================
    CREATE PROCEDURE [dbo].[spTruncate]
     @nameTable varchar(60) 

     


    WITH EXECUTE AS OWNER
    AS

    -- =============================================
     -- Author:  Yimy Orley Asprilla
     -- Create date: Julio 16 de 2014
     -- Update: September 21 2017
     -- Description: Función para hacer TRUNCATE a una tabla si ser owner de la tabla. con manejo de errores y SQL Inyection
     -- =============================================
    SET NOCOUNT ON

    DECLARE @strSQL VARCHAR(500);
    DECLARE @object_id int;

    SET @object_id = OBJECT_ID(@strTabla);

    BEGIN TRY
     IF @object_id IS NOT NULL
      BEGIN;
       BEGIN TRANSACTION;
       SET @strSQL = 'TRUNCATE TABLE [' + @strTabla + '];'
       EXECUTE (@strSQL);
       COMMIT TRANSACTION;
      END;
     ELSE
     BEGIN;
      PRINT N'La Tabla: ' + @strTabla + ' No existe';
     END;
    END TRY
    BEGIN CATCH 
        -- se presento un error en la ejcución y s epresenta
     PRINT N'Se presento el error: ';
        SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;  
    END CATCH;



    Friday, July 18, 2014 8:39 PM
  • 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;

    Apparently you have never heard of SQL injection.

    EXEC spTruncate '#temp; UPDATE Employees SET Salary = 2 * Salary WHERE EmployeeID = 1232'

    If you do something like this, you should either confine the procedure to a single statically defined table, or perform careful validation on the input parameter.

    Also, certificate signing is better from a security perspective than EXECUTE AS. See this article on my web site:
    http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, July 18, 2014 9:16 PM
  • Erland Thanks
    I share this modificactions to the SP

    SET

    ANSI_NULLSON

    GO

    SET

    QUOTED_IDENTIFIERON

    GO


     

     

     


    /****** Se validan el parametro de entrada @strTabla para evitar un SQL inyección, Yimy Asprilla ******/


    CREATE PROCEDURE[dbo].[spTruncate]

           

    @strTablaVARCHAR(100)


    WITH

    EXECUTEASOWNER

    AS


    -- =============================================


    -- Author:  Yimy Orley Asprilla


    -- Create date: Julio 16 de 2014


    -- Update: September 21 2017


    -- Description: Función para hacer TRUNCATE a una tabla si ser owner de la tabla. con manejo de errores y SQL Inyection


    -- =============================================


    SET

    NOCOUNTON



    DECLARE

    @strSQLVARCHAR(500);


    DECLARE

    @object_idint;



    SET

    @object_id=OBJECT_ID(@strTabla);



    BEGIN

    TRY


    IF@object_idISNOTNULL

    BEGIN;


    BEGINTRANSACTION;


    SET@strSQL='TRUNCATE TABLE ['+@strTabla+'];'


    EXECUTE (@strSQL);


    COMMITTRANSACTION;


    END;


    ELSE


    BEGIN;


    PRINTN'La Tabla: '+@strTabla+' No existe';


    END;


    END

    TRY

    BEGIN

    CATCH 

       

    -- se presento un error en la ejcución y s epresenta


    PRINTN'Se presento el error: ';


       

    SELECTERROR_NUMBER()ASErrorNumber,ERROR_MESSAGE()ASErrorMessage;  


    END

    CATCH;


    Thursday, September 21, 2017 8:01 PM
  • It still open to SQL injection.
    This is the correct way to do it:

    SELECT @sql = NULL
    SELECT @sql = 'TRUNCATE TABLE ' + quotename(s.name) + '.' + quotename(o.name)
    FROM   sys.schemas s
    JOIN   sys.objects o ON s.schema_id = o.object_id
    WHERE  o.object_id = object_id(@strTabla)
    IF @sql IS NULL
       RAISERROR('No such table %s', 16, 1, @strTabla)
    PRINT @sql
    EXEC (@sql)

    Also, next time you want to insert code: the UI has a difficult-to-find button for this, it's the one with two arrows pointing from each other.

    Thursday, September 21, 2017 9:27 PM