How to set the default schema of a database in SQL Server 2005?

Question

The following line works:

SELECT * FROM [myschema].users

But this does not:

SELECT * FROM users

Adam Wenger · Accepted Answer · 2015-08-11T00:23:33.893

73

A default schema is user-specific:

USE yourDatabase;
ALTER USER [yourUser] WITH DEFAULT_SCHEMA = myschema;

More information about the ALTER TABLE for SQL 2005 might help you as well.

As this is user-specific, if you have multiple users, you will need to execute this query (on each database) for each user whose default schema you want to update.

It is important to note:

The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin
fixed server role. All members of the sysadmin fixed server role have a default
schema of dbo.

edited Aug 11 '15 at 00:23

answered Nov 21 '11 at 06:36

Adam Wenger

16,424
6
50
63

Your scripts compile sucessfully but still the problem persist – Imran Qadir Baksh - Baloch Nov 21 '11 at 06:48
@user960567 I have tested this and it worked for me. (also just tested `schema_name()`) Did you give the default schema for the correct database? is the user you are trying to update linked to a sqlServer login? – Adam Wenger Nov 21 '11 at 06:53
Yes I have checked. The database is correct. The user have all permissions. – Imran Qadir Baksh - Baloch Nov 21 '11 at 06:56
@user960567 if by 'all permissions' you mean they are in the sysadmin fixed role, this doesn't work. `DEFAULT_SCHEMA` is ignored, and they use `dbo` – Adam Wenger Nov 21 '11 at 06:59
Thanks, This works for tables but not for Views. Can you tell me what is the problem? – Imran Qadir Baksh - Baloch Nov 21 '11 at 07:08
@user960567 I just added a view in my specified default_schema, tested fine. I'll keep thinking, but it is difficult since I cannot reproduce. Perhaps you could create a new test login & user, see what results you get with them? – Adam Wenger Nov 21 '11 at 07:12
Thank you very very much. I appreciate your help. – Imran Qadir Baksh - Baloch Nov 21 '11 at 07:23
1

This doesn't seem to work when the DB User has a backslash in the name. In that case, just use SQL Server Management Studio > Database > Security > Users > Properties and change the default schema there. – Chad Lehman Aug 10 '15 at 23:10
@ChadLehman, if you have odd characters in your user name (an NT Auth account, for example), you can always wrap the name in square brackets to ensure SQL Server handles the user name properly `[domain\ChadLehman]` as an example – Adam Wenger Aug 11 '15 at 00:22
7

"important to note" saved my day – Yiping Mar 13 '18 at 04:15

score 3 · Answer 2 · edited Nov 21 '11 at 07:04

3

You can use:

ALTER USER Mary51 WITH DEFAULT_SCHEMA = Purchasing;

To set the default schema for user.

edited Nov 21 '11 at 07:04

Pubby

50,432
13
129
175

answered Nov 21 '11 at 06:35

Ivan

31
1

1

just notice AdamWenger note [here](http://stackoverflow.com/a/8208124/426315) - `sysadmin` will always have `dbo` as default schema. – itsho Mar 28 '16 at 11:57

score 2 · Answer 3 · answered Jul 31 '19 at 07:46

Like user960567, I tried the answers from both Adam and Ivan and could not get it working.

I was running:

ALTER USER myuser WITH DEFAULT_SCHEMA=newschema

as suggested, but after executing this and executing

SELECT SCHEMA_NAME()

it was still returning 'dbo' as the default schema.

To fix this I executed:

ALTER USER myuser WITH DEFAULT_SCHEMA=newschema EXECUTE AS USER='myuser'

and it worked as expected - now executing:

SELECT SCHEMA_NAME()

returns 'newschema'.

How to set the default schema of a database in SQL Server 2005?

3 Answers3

Linked