Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Default Schema of Your Login Session

By: FYIcenter.com

(Continued from previous topic...)

What Is the Default Schema of Your Login Session?

When you login to a SQL Server and select a database to use, SQL Server will assign your login session a default schema. The schema name can be omitted when you refer to objects in the default schema. Here is what you should remember about default schema:

• The default schema of your login session in the current database is the default schema assigned to the current database level principal - database user.
• If you are referring to an object in the default schema, you do not need to specify the schema name.
• If you are referring to an object outside the default schema, you must specify the schema name.

The tutorial exercise below shows you how to verify your default schema:

-- Login with "fyi_login" 

USE FyiCenterData;
GO
Changed database context to 'FyiCenterData'.

PRINT User_Name();
GO
Fyi_User

SELECT name, default_schema_name 
   FROM sys.database_principals WHERE type = 'S';
GO
name                 default_schema_name
-------------------- --------------------
dbo                  dbo
guest                guest
INFORMATION_SCHEMA   NULL
sys                  NULL
Fyi_User             dbo

The last query shows that the default schema for "fyi_login" in "FyiCenterData" is "dbo".

(Continued on next topic...)

1. What Is a Schema in SQL Server 2005?
2. How To Create a New Schema in a Database?
3. How To List All Schemas in a Database?
4. How To Create a New Table in a Given Schema?
5. How To Transfer an Existing Table from One Schema to Another Schema?
6. How To List All Objects in a Given Schema?
7. What Is the Default Schema of Your Login Session?
8. Who Is the Owner of a Schema?
9. How To Change the Ownership of a Schema?
10. What Happens If You Are Trying to Access a Schema Not Owned by You?
11. How To Drop an Existing Schema?