Changing SQL Database Schema to DBO

Dear All

When we create any table / Stored Procedure / Views or any object in SQL Server default that object gets schema name of that database

 

e.g.

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

Begin

TSQL Statements

End

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

 

 

Thanks

 

Prashant Deshpande

 

 

Advertisements

Leave a comment

Filed under Uncategorized

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s