Default schema

  • Toreador

    SSChampion

    Points: 11259

    Bobby Russell (3/31/2014)


    We cannot assume the user is a sysadmin on the server.

    Nor can you assume that they are not (assuming you mean 'login' rather than 'user').

    Which is why the answer is "it depends".

  • victoria_e_wood

    SSC Enthusiast

    Points: 156

    In my opinion if the user was in fact a sysadmin why would you assign him or her rights over a database, they would automatically have rights over all databases...

  • SQLRNNR

    SSC Guru

    Points: 281252

    sestell1 (3/31/2014)


    Hmm, I'm surprised how many people are complaining about this question.

    The answer depends on the server role memberships of the login.

    Since no information provided regarding the login's server role memberships, it cannot be determined which schema the table would be created in without further information.

    +1

    king_login <> king_user

    I think too many people were assuming a typo there.

    Pretty good question in the end - unless you fell for the red herring.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • russ960

    SSCommitted

    Points: 1562

    +1 on SSCrazy. The question does not mention sysadmin membership assumption.


    Russ

  • Hany Helmy

    SSChampion

    Points: 13488

    Toreador (3/31/2014)

    In case you can't tell, I got the answer right 😀

    Me too 🙂

  • Hany Helmy

    SSChampion

    Points: 13488

    I think maybe this is the least correct answer percentage ever in QotD (8%)!

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    I have to agree with the others.

    I knew I had to make an assumption about being in the sysadmin role or not. I normally try to avoid assumptions - but when I have to make them, I tend to go with the most likely.

    From the question text, membership of sysadmin was unlikely. Not just because of the sp_addrolemember, but also because the best practice of creating specific logins to adminster specific databases is normally used to ensure that people can do their job without the sledgehammer access level of sysadmin.

    It would have been a good question if (1) the sysadmin role membership had been included in the question text, and (2) the answer would have included a link to a resource describing the behaviour. (To my surprise, the Books Online CREATE TABLE page does not mention anything on how the default schema for the new table is resolvedO))

    PS: Best practise = never rely on default rules for schema, always explicitly include it.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Uwe Ricken

    Hall of Fame

    Points: 3108

    Toreador (3/31/2014)Some people need to take a deep breath when getting a question wrong rather than immediately posting an angry response about all the points they've somehow had taken away from them!

    Do you really think its because of some internet points?

    I would expect a more accurate question and a well prepared question.

    If I post a question (until now only one!) I have done several tests and replaying all possible scenarios.

    Than i post my question and the possible solution.

    The reason for beeing "angry" (i would say disappointed) is that I have taken time to read the question, to understand the question and think about a solution. If I fail a question (and that were damned much in the past 🙂 ) it is o.k. if I could definitely say it is because of lack of knowledge. If it is like the current question it makes me "angry" to think about a problem which is none because not all aspects of the needed background have been given.

    EVERY candidate for QotD should prepare his question and the depending answers! That's what I'm missing at this special question!

    Microsoft Certified Master: SQL Server 2008
    MVP - Data Platform (2013 - ...)
    my blog: http://www.sqlmaster.de (german only!)

  • Toreador

    SSChampion

    Points: 11259

    victoria_e_wood (3/31/2014)


    In my opinion if the user was in fact a sysadmin why would you assign him or her rights over a database, they would automatically have rights over all databases...

    Good point 🙂

  • John Mitchell-245523

    SSC Guru

    Points: 148771

    Uwe Ricken (3/31/2014)


    Do you really think its because of some internet points?

    Well, at least five people have asked for their money back.

    You can argue talk all you like about what should and shouldn't have been included in the question, but you can't fault the logic. We don't know whether the login is sysadmin, so the answer is it depends.

    This isn't me being smug, by the way - I got the question wrong as well. The discussion makes it a good question - I learned something from it, whether or not I believe I could have worded the question better.

    John

  • sestell1

    SSChampion

    Points: 10230

    Toreador (3/31/2014)


    victoria_e_wood (3/31/2014)


    In my opinion if the user was in fact a sysadmin why would you assign him or her rights over a database, they would automatically have rights over all databases...

    Good point 🙂

    Sometimes a vendor app requires a database service account be temporarily added to the sysadmin roles because the installation software also creates the database.

    If you later assign database specific permissions but forget to remove the login from the sysadmin role, you could find some unexpected behavior such as the schema example in this question.

  • jlennartz

    SSCommitted

    Points: 1574

    Lynn Pettis (3/31/2014)


    Based on the information provided, the "correct" answer is wrong. There is nothing in the question to tell us that the login has been granted sysadmin privileges on the server.

    For once I agree, points should be awarded back.

    +1

  • Ken Wymore

    SSCoach

    Points: 16642

    I made the wrong assumption like most here. Now down to 6% correct. I think this may be the lowest correct percentage I have ever seen for a question that is almost 24 hours old! Apparently I need to work on my mind-reading certification.

  • danielfountain

    SSCarpal Tunnel

    Points: 4229

    Maybe the answer is nothing happens because the server isnt a server, but is infact a piece of cheese.

    Very strange answer....

  • mkeustermans

    Old Hand

    Points: 348

    Since king_login has default schema of king_schema and has been assigend the db_owner role and no other, the table will be created as king_schema.wonders.

    Points back please.

Viewing 15 posts - 31 through 45 (of 61 total)

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