Post reply

Same query returns different number of rows when run by a different login

  • November 21, 2022 at 9:41 pm

    Go to Answer

    #4116188

    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

    • This topic was modified 2 years, 6 months ago by 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

  • November 21, 2022 at 10:33 pm

    Answer

    #4116217

    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

    #4119106

    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 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy