What Is the Default Schema of Your Login Session in SQL Server?

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

 

⇒Creating and Managing Schemas in SQL Server

⇒⇒SQL Server Database Tutorials