Stored procedure permissions

Question

Stored procedure permissions

mcaster

Say Hey Kid

Points: 662
More actions
September 12, 2008 at 10:53 am

#94127

We created stored procedures on our outward-facing web site that are owned by 'dbo'. The user in the .asp pages is 'webuser' which has execute permission to all the necessary stored procedures but not the underlying tables. Most of the procedures work fine. The one which truncates a table does not. Can anyone tell me what I'm doing incorrectly?

Viewing 5 posts - 1 through 4 (of 4 total)

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

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

From BOL:

The minimum permission required is ALTER on table_name. 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. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.

So, check out the Execute AS clause but understand the ramifications of that as well.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

truncate is one of those strange ones. I'd go with David's suggestion of Execute AS if you must do this.

mcaster Say Hey Kid Points: 662 More actions · Answer 3

Thanks a bunch for your response. I am sure it will work out from here with the 'execute as' statement (once I read up on it). Would have hunted for that a long time without your help.

mcaster Say Hey Kid Points: 662 More actions · Answer 4

Thanks for your reply also. I read the daily email faithfully and look forward to meeting you at PASS.