Post reply

Stored Proc execute permission not enough?

  • January 14, 2004 at 1:12 pm

    #358915

    I have a stored procedure that is to be executed by a DTS package remotely by a team member that has minimal permissions in the database.  The stored procedure drops a table and recreates it for staging of data in a transformation.  I understood that all a user needed was execute permissions to a stored procedure regardless of what was in the proc itself.  When the user runs the package an error message is generated indicating they do not have permission to drop the table.

    Why isn't execute on stored proc enough?

  • January 14, 2004 at 1:19 pm

    #490053

    Who is the owner of the table? Why drop and recreate table instead of truncating the table?

  • January 14, 2004 at 1:21 pm

    #490054

    DROP TABLE permissions default to the table owner, and are not transferable. However, members of the sysadmin fixed server role or the db_owner and db_dlladmin fixed database roles can drop any object by specifying the owner in the DROP TABLE statement.

  • January 14, 2004 at 2:07 pm

    #490068

    Truncate table has the same permissions as DROP TABLE.  The package is to be executed by a user that has minimal permission in the database, not a sysadmin, db_owner or db_dlladmin. 

    I thought the whole point was that if the user has execute permission on Stored Proc then they don't need the underlying permissions.

     

  • January 14, 2004 at 2:31 pm

    #490077

    Yes or No. In this case, you have to perform some DDL operation to the table user doesn't own. He/She needs at least db_ddladmin fixed database roles.

     

     

  • January 14, 2004 at 2:42 pm

    #490083

    So is TRUNCATE TABLE considered DDL as well?

     

  • January 14, 2004 at 4:19 pm

    #490097

    TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table. It needs at least db_ddladmin fixed database roles as you already knew.

    I wouldn't consider it as DDL operation.

    Data Definition Language (DDL)

    The SQL language has two main divisions: Data Definition Language (DDL), which is used to define and manage all the objects in an SQL database, and Data Manipulation Language (DML), which is used to select, insert, update, and delete data in the objects defined using DDL. The Transact-SQL DDL used to manage objects such as databases, tables, and views is based on SQL-92 DDL statements, with extensions. For each object class, there are usually CREATE, ALTER, and DROP statements, such as CREATE TABLE, ALTER TABLE, and DROP TABLE. Permissions are controlled using the SQL-92 GRANT and REVOKE statements, and the Transact-SQL DENY statement.

     

  • January 14, 2004 at 4:20 pm

    #490098

    From BOL

    "TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable."

     

    mom

  • January 15, 2004 at 4:19 pm

    #490318

    If you do not want to give the users running the DTS packages DDL permissions, modify the DTS to move all those tables to a new work database and give the operators DDL permissions in the database. You can move them to the tempdb also, but in tempdb, if the server is restarted in the middle of your DTS execution the table is lost. I would say have a work database and use it for all your tables that are truncated or dropped.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply