Not Again!

  • Thomas Abraham

    SSChampion

    Points: 10761

    Brian.Klinect (2/18/2015)


    I learned to today that I can set permissions at the column level. Never even considered trying to do that before.

    I really liked the story that went with the question. Keep up the good storytelling!

    Also typical when needing to access employee information (address, phone, etc.) to prevent people from also seeing salary information, for example.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • sknox

    SSChampion

    Points: 12294

    Dave62 (2/18/2015)


    I got it right but I don't think Steve got the answer to his question.

    Steve asked the simplest request: how many users on the website?

    I would interpret that question to mean how many users are currently logged on to the website, which is most likely not the total number of ID's in the users table. It's probably a rare occasion, if ever, that all users are logged on at the same time.

    😉

    Then Steve needs to learn to ask questions more clearly 😉

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the question. It was a good one.

  • TomThomson

    SSC Guru

    Points: 104773

    Nice question. Easy, since 230 is a permission failure error. I'm inclined to agree with the person who suggested this behaviour is a bug (especially for select count(<constant>), but it maybe oughtn't to happen for count(*) either), but I suspect it's been there since SQL Server 7 or earlier and if it hasn't been fixed yet I doubt it ever will be.

    Tom

  • Brian Hibbert

    Ten Centuries

    Points: 1322

    I don't think it's a bug.

    Both Select Count(*) and Select Count(1) would generate the same index scan as a Select *. I'd guess that the permission check is done before the scan so if the user isn't allowed access to any of the columns in the table the permission check fails. If you are specifying a column that the user does have access to, the check passes and the plan is executed.

    It's just a guess. I didn't write the code and don't have access to source code so I can't verify any of this.

  • Charles Kincaid

    SSChampion

    Points: 13593

    I'd love to hear from the SQL Server internals teams. I am wondering if this is the desired behavior and simply needs more documentation.

    We have always been warned that we can't count on the order of rows coming into a query. Note that I said into. We control the order of rows going out of the query with ORDER BY. Likewise we should not count on the order of columns coming out when we use asterisk. I'm wondering if the column denied was the first one in the column number order on the table. If not is it then that if the user has denied permission on any column does this occur?

    ATBCharles Kincaid

  • webrunner

    SSC-Dedicated

    Points: 30366

    Great question - I learned something important from this one. Thanks!

    - 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

  • pmadhavapeddi22

    SSCertifiable

    Points: 5306

    thanks for the nice question

Viewing 8 posts - 16 through 23 (of 23 total)

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