August 13, 2008 at 9:37 am
Hi,
I've got a data warehouse which is effectively a star schema. However, one of the dimensions requires row-level security to prevent access to some of the data.
I've essentially achieved this by using a view to combine the underlying dimension (table) and another table (which basically contains a list of all the surrogate keys in the dimension that each user has access to) to get the correct level of access. The view then uses the SUSER_NAME() function to create the "secure view" of the dimension.
Now, different people can see different quantities of data ie some may see all, some may see a subset and some may see only one row.
When I run these queries in SSMS I get different query plans every time depending on the number of rows, usually an index scan of the dimension table for a large number of rows or a seek for a small number of rows. I'm wondering what's happening when i'm not looking ie does the execution plan get cached in the case that SUSER_NAME() is being used?
Thanks,
Mark
August 13, 2008 at 11:36 am
If the first guy through creates a plan that uses a scan, unless something fires a recompile of the procedure or the plan ages out of cache, it's going to stay in place. You might want to take a look at the OPTIMIZE FOR query hint as a way to avoid this issue.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply