July 20, 2012 at 11:34 am
I have a sproc that performs a multi-table insert. Some of which is done via dynamic SQL. When my developer executes the sproc (he has execute permission) he receives an insert error on one of the many tables being used. Interesting I can't reproduce the error in dev (using a login that has similar rights). The table has triggers and constraints but I'm loathe to disable them if I don't have to. Does anyone have a clue what could be happening or an idea of what I should do to troubleshoot? I'm at a loss.
Thanks in advance
Jerry
July 20, 2012 at 11:50 am
Dynamic SQL breaks the ownership chain. The user executing the proc requires permissions on the base tables as well.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 20, 2012 at 12:10 pm
GilaMonster (7/20/2012)
Dynamic SQL breaks the ownership chain. The user executing the proc requires permissions on the base tables as well.
I thought I had read something to that affect. 2 follow-up questions:
- Is it odd that of the 4 tables he is using dynamic SQL against it would report an insert error on the 3rd only?
- Is there any way around this permissions issue other than giving the user direct table access (which isn't procedural possible).
Thanks Gail for the quick response your awesome!
July 20, 2012 at 4:14 pm
jerrycutshaw (7/20/2012)
- Is it odd that of the 4 tables he is using dynamic SQL against it would report an insert error on the 3rd only?
He's already got permissions somehow on the others. Some role or group.
- Is there any way around this permissions issue other than giving the user direct table access (which isn't procedural possible).
No. The user running the procedure needs rights on the base tables. You can use EXECUTE AS on the procedure to change which user the proc executes as.
Otherwise don't use dynamic SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2012 at 2:29 am
CELKO (7/21/2012)
Triggers are another bad code smell. It uses procedural code to fix design errors on the fly when you could not think of a declarative solution. Mother Celko's rule of thumb is that you should write no more than FIVE triggers in your entire career.
I thought that rule was for CURSORs! Oh my, I've bent my mother's thumb. :w00t:
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply