Scribd
Upload
8 views6 pages

Understanding SQL Default Schema DBO

The document explains the concept of the dbo user and schema in SQL databases, highlighting that dbo is the default user with full permissions and cannot be deleted. It describes schemas as collections of database objects that help organize and protect data, and outlines the advantages of using schemas for managing database objects. Additionally, it provides instructions on creating schemas and tables within a database.

Uploaded by

chauhanpankaj0501
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
8 views6 pages

Understanding SQL Default Schema DBO

The document explains the concept of the dbo user and schema in SQL databases, highlighting that dbo is the default user with full permissions and cannot be deleted. It describes schemas as collections of database objects that help organize and protect data, and outlines the advantages of using schemas for managing database objects. Additionally, it provides instructions on creating schemas and tables within a database.

Uploaded by

chauhanpankaj0501
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SQL-DBO User

dbo user is the default user created by default with its schema in every
database.

A user that has implied permissions to


perform all activities in the database.

DBO ?
Database Owner Member of the sysadmin fixed server
role who uses a database is mapped to
the special user inside each database
called dbo.

The dbo user cannot be


deleted and is always present
in every database.
SQL-Schema
 A schema is a collection of database objects including tables, views, triggers, stored
procedures, indexes, etc.
 "dbo" stands for database owner.
 dbo schema is the default schema created by default in every database.
 A schema is associated with a username( dbo by default ) which is known as the schema
owner, who is the owner of the logically related database objects.
 If you don't create another schema, every object is automatically created using the dbo
schema.
To list all schemas in the current database

SELECT [Link] AS schema_name, [Link] AS schema_owner


FROM [Link] s INNER JOIN [Link] u ON [Link] = s.principal_id ORDER BY [Link];
SQL-Schema
Company Database
Different Schemas
Owner : DBO

Accounts HR Sales

 Tables  Tables  Tables


 Emp  Emp  Emp
 Students  Students  Students
 Sales  Sales  Sales
 Stored Procedure  Stored Procedure  Stored Procedure
 Fetch_Data  Fetch_Data  Fetch_Data
 Fetch_Result  Fetch_Result  Fetch_Result
Advantages of using Schema

 Act as object protection tool: A schema can be a very effective object protection tool
combined with the appropriate level of user permissions. A DBA can maintain control access
to an object that would be very crucial.
 Managing a logical group of database objects within a database: Schemas allow database
objects to be organized into a logical group. This would be advantageous when multiple
teams are working on the same database application and the design team wants to maintain
integrity of the database tables.
 Easy to maintain the database: A schema allows a logical grouping of the database objects,
so the schema can help us in situations where the database object name is the same but falls
in a different logical group.
 Default schema

 The default schema is the first schema searched when resolving object names. The user can be defined within the
default schema. Using the "SCHEMA_NAME" function we can determine the default schema for the database.

SELECT Schema_name() ;

 How to create a schema

 Step-1
Create a new database
Create Database Schema_db;

 Step-2
 Choose Schema_db;
 Create new schemas

Create Schema Sales;


Create Schema Accounts;
 Step-3 : Creates tables

Create Table emp


(
Emp_ID INT,
Name Varchar(100)
);

Create Table [Link]


(
Emp_ID INT,
Name Varchar(100)
);

Create Table [Link]


(
Emp_ID INT,
Name Varchar(100)
);

Insert into emp Values(1,'Ajay');


Insert into [Link] Values(1,'Ajay');
Insert into [Link] Values(1,'Ajay');

You might also like