January 4, 2022 at 3:29 pm
I'm running into the following issue.
The stored procedure I need to performance tune uses a user without login.
This is needed for row-level security.
But the problem I ran into is that I cannot give this user showplan rights in the tempdb database.
Does anyone have a solution or workaround for this?
January 4, 2022 at 4:38 pm
What I would do is restore live to a dev environment. Once things are on the development environment, run the stored procedure a few times as is so you can get some performance data (execution time), change the stored procedure to run as you and tune it. Once it is tuned to your liking, add that account back in and do another performance test to make sure your tuning decreased execution time.
I would NEVER try to tune something on live! If you are not supposed to look at the data (row level security stuff), you can't easily tune it as is. The way to get around that is with some data obfuscation tools/techniques where post refresh, any tables with confidential data get dummy data dumped into them. That way you can tune the query and not worry about seeing anything you are not supposed to see. Those should already be in place though so you can do development work and can verify that your creations (views, stored procedures, triggers, whatever) are giving accurate results. If, for example, one of the hidden columns is that an end user can only see their own salary, your query that you would design would be based on your data in that table. This MAY exclude certain things that others MAY be getting and could result in incorrect data. Such as if you are salaried in an IT related role, so your view on that table may not include overtime or commissions or bonuses. Then if you are building the query up and don't know about all of the possible data points, it could be really easy to have the query work great for you and give bad data OR have horrid performance for someone with more data points.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply