Why is my view created in my user schema?

Question

I'm using MSSQL Studio and am connected to a SQL Server 2014 with my windows credentials (DOMAIN\user).

I just created a new view and wondered why another user could not find it:

CREATE VIEW abc AS SELECT 1 AS a

Unexpectedly this view was not created in dbo schema but in DOMAIN\user. To create the view in dbo schema I needed to execute:

CREATE VIEW dbo.abc AS SELECT 1 AS a

This kind of surprises me, for I've been creating views for ages without explicit schema and have always been created in dbo – since today.

What could be the reason?

Your SQL Server login will be mapped to a user in the database. What is the default schema for the database user? — Richard, Sep 21 '17 at 12:40
Security > Users > {user} right-click - Properties > General ... Default Schema — John Cappelletti, Sep 21 '17 at 12:49
or simply add the schema CREATE VIEW dbo.abc AS SELECT 1 AS a — John Cappelletti, Sep 21 '17 at 12:51
@Richard and @John, you are both right: The default schema for my user was `DOMAIN\user` instead of `dbo`. I fixed that and now all is fine again. Thank you both! — Spock, Sep 21 '17 at 13:45

score 1 · Accepted Answer · answered Sep 21 '17 at 13:48

1

Check under Security -> Login -> Your User - User Mapping. For all selected maps your "Default Schema" should be changed to dbo.

answered Sep 21 '17 at 13:48

Denis Luiz

169
6

score 1 · Answer 2 · answered Sep 21 '17 at 22:20

1

You can change your default schema to dbo, but as a best practice views should be scripted to include the schema. In the case of dbo, it's 4 extra characters with the period but it prevents these types of issues.

answered Sep 21 '17 at 22:20

EricI

3,636
1
16
8

Why is my view created in my user schema?

2 Answers2