Default schema

  • 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".

  • 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...

  • 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

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


    Russ

  • Toreador (3/31/2014)

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

    Me too 🙂

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

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

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • 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/

  • 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!)

  • 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 🙂

  • 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

  • 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.

  • 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

  • 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.

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

    Very strange answer....

  • 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 60 total)

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