Significance of using dbo in create statement

Question

Significance of using dbo in create statement

ruchi-761283

SSC-Addicted

Points: 434
More actions
November 3, 2009 at 10:59 pm

#136105

Hi,
I have one query,suppose I create a store procedure in sql 2005 using below statement :
create procedure dbo.<procname>
as
( some stmts)
end
Please help me in understanding the significance of dbo used in the create statement.
Also what will be the difference in access rights with or without using dbo.
Thanks in advance...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

Using dbo specifies the schema. But since dbo is the default, you don't really need to specify it unless you have stored procedures with the same name under a different schema. If you use a non-default schema, then you do need to specify it, ie MySchema.mysp_StoredProc

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

dbo is the default, usually.. It is considered a best practice to always include the schema of the object you are creating AND accessing. It very clearly determines what schema you are refering to. Now most of us have to create other schemas or have different schemas as our user default for this to be an problem. But consider this, if you execute a CREATE statement without explicitly specifying the schema, what schema will it be in? You cannot guarantee dbo in all cases. This is also true for object access, ie: SELECT, you should be specifying the schema when you access an object, one you get a marginal performance increase because the DB engine doesn't have to think about what schema you are talking about, it already knows..

Given this, I would recommend that for all DDL and DML statements that allow the prefixing of schema, that you specify it.

CEWII

ruchi-761283 SSC-Addicted Points: 434 More actions · Answer 3

Hi,

Many thanks for your help.

But if I am specifying DBO as schema of this store proc, who all users will be able to access this?

and If I specify my own scema then who will able to execute this store procedure?

Thanks in advance.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

Permissions is a bit trickier.. In general users do not have access to a stored procedure unless you grant EXEC rights to their user or a database role they are in. If a user or role is granted db_owner rights then they can, or any user granted the system role sysadmin will generally be able to run it..

CEWII

mstjean Hall of Fame Points: 3310 More actions · Answer 5

I was seeing objects in a db that were in user schemas and it took a bit to figure out why. It happened when they were not explicitly using "dbo" when creating objects.

This is because of the way I use domain groups.

For example, we have the SQL_DEVELOPERS group defined in our domain. As people revolve in/out of our environment-- they are just added to/removed from this domain group. In SQL I add SQL_DEVELOPERS as a login on the server, and as a user in required dbs. (Note that you cannot set a default schema for this domain group! This was the key to the puzzle) Then I create ROLE_DEVELOPERS in the db and add SQL_DEVELOPERS to it, and set permissions in the role.

The side effect is that if a user creates a db object without specifying the schema name it creates it under that users login... it was a bit confusing finding an object named JoeBlow.Tablename when JoeBlow was never explicitly added as a db user-- he has access only by virtue of his membership in SQL_DEVELOPERS in the domain. (iirc it also created the USER on the fly when this happened. After changing JoeBlow.Tablename to dbo.Tablename I was able to drop the user with no ill effect)

Cursors are useful if you don't know SQL