September 12, 2008 at 10:53 am
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?
September 12, 2008 at 11:03 am
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
September 12, 2008 at 11:11 am
truncate is one of those strange ones. I'd go with David's suggestion of Execute AS if you must do this.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 12, 2008 at 11:23 am
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.
September 12, 2008 at 11:25 am
Thanks for your reply also. I read the daily email faithfully and look forward to meeting you at PASS.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply