Default Schema of Your Login Session in SQL Server


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;
Changed database context to 'FyiCenterData'.

PRINT User_Name();

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

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


Who Is the Owner of a Schema in SQL Server

"sys.objects" - Listing All Objects in a Given Schema in SQL Server

Creating and Managing Schemas in SQL Server

⇑⇑ SQL Server Database Tutorials

2016-10-22, 1618🔥, 0💬