How to change default schema ?

Hi,

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.

Phil
s2000_comAsked:
Who is Participating?
 
s2000_comConnect With a Mentor Author Commented:
SQL_SERVER_DBA :
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" ?
0
 
SQL_SERVER_DBACommented:
each user can be assigned a default schema but you still should specify the schema when creating a table.

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

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