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?