How to change default schema ?


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.

Who is Participating?
s2000_comConnect With a Mentor Author Commented:
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" ?
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)

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.