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

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

Related Articles:


Other Tutorials/FAQs:


Related Resources:


Selected Jobs: