Hello experts,
Does anyone know how the same view query could return a different number of rows depending on which user is running it?
I'm facing that issue now. I tried making sure the user in question has SELECT access to the tables in the view. Still different totals. What is strange is a lack of access should throw an error, not just silently return fewer rows.
I have narrowed down the discrepancy to a part of the view that has the format AND this_id IN (SELECT ...). But I don't know what in that subquery / derived table is causing the difference.
I am using EXECUTE AS to impersonate the user that is getting the result with too few rows.
I then tried to look up whether Row-Level Security is enabled, and found sys.security_policies. But that view has no rows, and I am not even sure it is relevant to this issue. It's just the closest thing I found to having the same SELECT query return different rows depending on the user.
Does anyone have any advice on how I can proceed? Thanks for any help.
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
you need to dig through all tables, views and functions that query uses.
it is highly possible that one of them has a filter on the users (common for MS CRM for example) or it does not specify the schema of an object and it is using a different input depending on the user that executes it.
even having a different default schema on the 2 users could be the cause of that difference (if code does not use schema.object throughout)
November 28, 2022 at 6:48 pm
Thanks, Frederico. This was indeed ultimately a permissions issue.
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply