When we create any table / Stored Procedure / Views or any object in SQL Server default that object gets schema name of that database
Database Name :-abcd
DB owner user :- xyz
DB schema :- xyz
A database schema is a collection of meta-data that describes the relations in a database. A schema can be simply described as the “layout” of a database or the blueprint that outlines the way data is organized into tables. Schema are normally described using Structured Query Language as a series of CREATE statements that may be used to replicate the schema in a new database.
When we are creating a new Stored procedure e.g.
Create proc sample as
After this statement the procedure will be seen as
Xyz.sample where xyz is the schema name of that database.
Here the issue is that while executing the stored procedure it will throw error as the default schema is not dbo, to change the database schema
from xyz to dbo we need to do following steps.
Open SQL Server Management Studio à Expand Security à right click on User , select Properties à click on User Mapping
If you observe on the right top corner it is showing the database schema “Guest” and to access the objects created by this user afterwards the default schema should be DBO and to do it so click on right corner button in the default schema column ( here beside guest ) , it will open following screen
Click on browse button and select DBO from the list
And click on OK button .
After this any object created by the user xyz will be accessed with dbo schema J