Post reply

Not Again!

  • February 18, 2015 at 4:19 pm

    #1778507

    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

  • February 18, 2015 at 4:31 pm

    #1778509

    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 😉

  • February 19, 2015 at 7:38 am

    #1778635

    Thanks for the question. It was a good one.

  • February 19, 2015 at 2:54 pm

    #1778785

    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

  • February 20, 2015 at 8:47 am

    #1778971

    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.

  • February 25, 2015 at 2:00 pm

    #1780062

    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

  • February 26, 2015 at 7:29 am

    #1780256

    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

  • March 26, 2015 at 4:04 am

    #1786493

    thanks for the nice question

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

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