locked
What minium permission is need to able to Truncate tables with Stored Procedure? RRS feed

  • Question

  • I wish to not to give db_owner rights to user if possible so created a SP for truncate tables.

    I FOLLOWING RIGHTS TO THE USER:
    db_reader
    db_writer

    I HAVE STORED PROCEDURE:
    CREATE PROC [dbo].[Truncate_test_tables]
     WITH EXECUTE AS OWNER
     AS
      Truncate table [databasename].[dbo].[test]
    GO

    I TRIED TO EXECUTE:
    exec truncate_test_tables

    HOW TO SOLVE FOLLOWING ERROR RETURNED BY EXECUTING SP?
    Cannot find the object "test" because it does not exist or you do not have permissions.

    TEST OK: (return values succesfully)
    select * from Test

    TEST OK: (expected result as wish to have minimal permission for the user)
    truncate table test
    Cannot find the object "test" because it does not exist or you do not have permissions


    Kenny_I

    Thursday, May 23, 2013 5:52 AM

Answers

  • You can grant EXEC permission on the stored procedure.

    GRANT EXEC ON some_sp TO user-- Grant permission on a single procedure.
    GRANT EXEC ON SCHEMA::dbo TO user -- Grant perpmission on all procedures in
                                          the dbo schema
    GRANT EXEC TO user-- Grant EXEC permission all procedures in the database.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, May 23, 2013 6:41 AM
  • You need ALTER permission to perform TRUNCATE TABLE. Ownership chaining does not apply in this case, but the user must have direct permission for the operation.

    A good solution is to sign the procedure with a certificate, create a user from that certificate, and grant that user ALTER on the table.

    I have an article on my web site where I describe this technique in detail:
    http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Fanny Liu Friday, May 24, 2013 8:50 AM
    • Marked as answer by Fanny Liu Tuesday, May 28, 2013 4:41 AM
    Thursday, May 23, 2013 9:47 PM

All replies

  • You can grant EXEC permission on the stored procedure.

    GRANT EXEC ON some_sp TO user-- Grant permission on a single procedure.
    GRANT EXEC ON SCHEMA::dbo TO user -- Grant perpmission on all procedures in
                                          the dbo schema
    GRANT EXEC TO user-- Grant EXEC permission all procedures in the database.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, May 23, 2013 6:41 AM
  • You need ALTER permission to perform TRUNCATE TABLE. Ownership chaining does not apply in this case, but the user must have direct permission for the operation.

    A good solution is to sign the procedure with a certificate, create a user from that certificate, and grant that user ALTER on the table.

    I have an article on my web site where I describe this technique in detail:
    http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Fanny Liu Friday, May 24, 2013 8:50 AM
    • Marked as answer by Fanny Liu Tuesday, May 28, 2013 4:41 AM
    Thursday, May 23, 2013 9:47 PM