How to change default schema ?

Posted on 2007-10-16
Medium Priority
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

ID: 20086033
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

s2000_com earned 0 total points
ID: 20086105
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
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 shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 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