locked
non-default schema in sql server 2016 RRS feed

  • Question

  • My goal is to run query without specifing the non-default schema.

    Select * form employees --> But not select * from abc.employees.

    For testing i created one sql login and assigned sysadmin role. But it didn't work.

    At the momement when i remove sysadmin rights and ran the select * from employees it's worked fine.

    Sysadmin role is highest role why it's not recognize non-default schema.

    Tuesday, July 23, 2019 11:46 PM

Answers

  • For testing i created one sql login and assigned sysadmin role. But it didn't work.

    At the momement when i remove sysadmin rights and ran the select * from employees it's worked fine.

    I assume that you created the SQL login and mapped it to a database user with the default schema abc.

    However, if you are member of sysadmin, your login will always map to the dbo user in all databases, and the default schema for dbo is always dbo.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by VijayKSQL Wednesday, July 31, 2019 5:48 PM
    Wednesday, July 24, 2019 7:37 AM

All replies

  • Hi VijayKSQL,

     

    >>Sysadmin role is highest role why it's not recognize non-default schema.

     

    Sysadmin role is highest role, so using this role you can view the tables in all schemas in the database. We can create tables of the same name belonging to different schemas in the same database.

    If you are in the sysadmin role, do not specify the schema, sql server will not be able to determine which table you want to query.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, July 24, 2019 2:19 AM
  • When it is a "non-default" schema then how do you expect SQL Server to work out which schema to use?

    When a schema name is not specific, it will first look in the sys schema then the default schema for that user and then will look in dbo. It does not look anywhere else.

    If your object is in a non-default schema which is neither of sys nor dbo then it will not be able to resolve this.

    If you DO specify the schema then the query goes directly to that specific object - saving additional lookups on the system tables to see if there is an object with that name in the sys or dbo schemas - meaning it will be quicker.

    I cannot see any reason why you would not want to include the schema name - it is best practice.


    Martin Cairney SQL Server MVP

    Wednesday, July 24, 2019 2:53 AM
  • My goal is to run query without specifing the non-default schema.

    Not possible, you must address the (non-default) Schema. I always add the Schema to object Name, even if it is the default schema; that's simply best practice.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, July 24, 2019 5:46 AM
  • For testing i created one sql login and assigned sysadmin role. But it didn't work.

    At the momement when i remove sysadmin rights and ran the select * from employees it's worked fine.

    I assume that you created the SQL login and mapped it to a database user with the default schema abc.

    However, if you are member of sysadmin, your login will always map to the dbo user in all databases, and the default schema for dbo is always dbo.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by VijayKSQL Wednesday, July 31, 2019 5:48 PM
    Wednesday, July 24, 2019 7:37 AM