Stored Proc execute permission not enough?

Question

Post reply

Stored Proc execute permission not enough?

andersg98

SSCertifiable

Points: 5180
More actions
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?

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

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

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

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

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 2

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.

andersg98 SSCertifiable Points: 5180 More actions · Answer 3

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.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 4

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.

andersg98 SSCertifiable Points: 5180 More actions · Answer 5

andersg98

SSCertifiable

Points: 5180

January 14, 2004 at 2:42 pm

#490083

So is TRUNCATE TABLE considered DDL as well?

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 6

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.

Sopheap Suy SSCertifiable Points: 7893 More actions · Answer 7

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

mssql_rules SSCrazy Points: 2783 More actions · Answer 8

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.