May 8, 2008 at 8:45 am
A user is getting this error when trying to execute a stored procedure that truncates Table_X:
"Table_X does not exist or you do not have permissions."
I've verified that the user has execute permissions on the SP. Isn't that sufficient? They don't specifically need delete (truncate) permission on the table itself, correct? Color me confused on this one...
"Got no time for the jibba jabba!"
-B.A. Baracus
May 8, 2008 at 8:56 am
ahutchens (5/8/2008)
A user is getting this error when trying to execute a stored procedure that truncates Table_X:"Table_X does not exist or you do not have permissions."
I've verified that the user has execute permissions on the SP. Isn't that sufficient? They don't specifically need delete (truncate) permission on the table itself, correct? Color me confused on this one...
As long as the ownership chain isn't broken execute permission should be enough for select, update, insert or delete. Using truncate is different, because truncate is seen as a ALTER TABLE action which only db_owner and ddl_admin are allowed to execute.
If you really need a truncate in your stored procedure, use the EXECUTE AS option.
[font="Verdana"]Markus Bohse[/font]
May 8, 2008 at 9:01 am
Ok, so it's TRUNCATE specifically, I was just generalizing with delete. So, for any SP to use TRUNCATE, if it isn't being executed by db_owner or ddladmin, would have to use EXECUTE AS? (The user is datareader and datawriter, currently)
Just trying to understand all this - I kind of thought SP's sort-of had their own "permissions", in that as long as you could execute the SP, that was what mattered. So that's incorrect?
"Got no time for the jibba jabba!"
-B.A. Baracus
May 8, 2008 at 9:38 am
You have the right idea. The difference is that TRUNCATE TABLE is a "non-transferable" permission, unlike SELECT, DELETE, UPDATE, and INSERT. See "TRUNCATE TABLE (Transact-SQL)" in BOL.
Greg
May 8, 2008 at 10:54 am
That was very helpful, thanks!! BOL to the rescue, again.
"Got no time for the jibba jabba!"
-B.A. Baracus
May 8, 2008 at 11:32 am
ahutchens (5/8/2008)
Ok, so it's TRUNCATE specifically, I was just generalizing with delete. So, for any SP to use TRUNCATE, if it isn't being executed by db_owner or ddladmin, would have to use EXECUTE AS? (The user is datareader and datawriter, currently)Just trying to understand all this - I kind of thought SP's sort-of had their own "permissions", in that as long as you could execute the SP, that was what mattered. So that's incorrect?
Sprocs have the rights ("permissions") of their Execution-principals, which is usually the sames as the Sproc's Owner (unless it is changed with EXECUTE AS), which is usually the same as the Schema (unless it is changed with ALTER AUTHORIZATION ON..). So besides EXECUTE AS, you could also change the Sproc's schema or Owner to 'dbo' or any other that has those rights, or grant those rights to the Sproc's current owner.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 8, 2008 at 11:50 am
The proc is part of the dbo schema, so I think the owner is dbo, and dbo is associated with sa. That fact didn't make any difference though, because the user executing the proc does not have ALTER permissions on that table.
They added EXECUTE AS OWNER in the proc and now it works.
"Got no time for the jibba jabba!"
-B.A. Baracus
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply