Execution Plan Caching and SUSER_NAME()

  • 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

  • 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