How to alter default_schema

Corey Fleig 100 Reputation points
2025-08-01T19:14:09.6033333+00:00

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"?

SQL Server | SQL Server Transact-SQL
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 124.3K Reputation points MVP Volunteer Moderator
    2025-08-01T19:37:34.3933333+00:00

    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
    

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.