How to alter default_schema

Question

Here's my problem:

1. Logged into SQL Server with default Windows authentication, Username = [mydomain\CoreyF]
2. Created a table "mytable"
3. Executed "SELECT * FROM INFORMATION_SCHEMA.TABLES"
4. The default schema shows "[mydomain\CoreyF]"
5. Logged into SQL server with admin privileges
6. Executed "ALTER USER [mydomain\CoreyF] WITH DEFAULT_SCHEMA = dbo;
7. Logged out, and back into my account with windows auth.
8. dropped and created the same table
9. The default schema remains [mydomain\CoreyF]

How can I get it to be "dbo"?

Answer 1

That is indeed the correct command. I suspect that you are making some mistake when you run the command, like logging on to the wrong server, or running the command in the wrong database.

Here is a repro which demonstrates that the command works. In this example I'm creating a user without login for simplicity, and to contain everything in one script, I impersonate the user.

CREATE USER testie WITHOUT LOGIN WITH DEFAULT_SCHEMA = testie
GRANT CREATE TABLE TO testie
GRANT ALTER ON SCHEMA::dbo TO testie
go
CREATE SCHEMA testie AUTHORIZATION testie
go
EXECUTE AS USER = 'testie'
go
CREATE TABLE tbl1(a int NOT NULL)
go
REVERT
go
ALTER USER testie WITH DEFAULT_SCHEMA = dbo
go
EXECUTE AS USER = 'testie'
go
CREATE TABLE tbl2(a int NOT NULL)
go
REVERT
go
SELECT * FROM INFORMATION_SCHEMA.TABLES
go
DROP TABLE dbo.tbl2
DROP TABLE testie.tbl1
go
DROP SCHEMA testie
DROP USER testie