How to change default schema ?

Posted on 2007-10-16
Last Modified: 2008-04-15

With Management Studio, I created an SQL login (mylogin) then I created a schema (myschema) and finally a user (myuser) with "mylogin" as login name and "myschema" as default schema.

When I log-in and create a new table thru the Management Studio or thu a script without a schema prefix, "dbo" schema is used. Why ?

Note : as I selected "myschema" as default shema, the table has to be created in "myschema" schema. No ?

Thanks for ur future reply.

Question by:s2000_com
    LVL 16

    Expert Comment

    each user can be assigned a default schema but you still should specify the schema when creating a table.

    CREATE TABLE Sales.SalesPeople
        SalesPersonId INT,
        SalesPersonName VARCHAR(50)


    Accepted Solution

    So what's the use of default schema ?

    You mean that I cannot change the default behavior when crerating a table from Management Studio : in the properties of the new table > Schema : it will be always "dbo" ?

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now