May 31, 2012 at 7:38 am
I have an application that assembles a SQL query and sends it to the SQL Server engine for execution. The query is fairly complex, involving a number of joins and possible conditions. It joins around 20 auxilliary tables to the main table, and I use Left Join for tables that may or may not have a linking entry, but I don't care, and Inner Join with a condition, when I am filtering by something in one of the aux tables. I used to filter in the application on the results of the massive join, but that's slow, since the engine has to assemble the entire mess before deciding which records it doesn't want.
This is much faster, but since the query is so complex, most of the query text must be passed from the app, which raises the obvious concern of injection attacks. Is it possible to restrict the rights of a stored procedure, so that I can say somehow, 'THIS stored procedure has read-only access to ONLY THESE tables, regardless of what code is actually in the SP.'?
May 31, 2012 at 7:43 am
Read up on EXECUTE AS.
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
May 31, 2012 at 7:47 am
GilaMonster (5/31/2012)
Read up on EXECUTE AS.
Got it - that's exactly what I need. Thanks for the kick in the right direction.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply