'dbo' definition 3

[Skittle]

I am just starting with SQL Server.
Can somebody define for me what 'dbo' stands for?
It pops up in books and technical pages but it is never really defined.

I suspect it means 'Database Owner' but I'm not
certain. If so, does it mean you can explicitly specify a user where ever you see the 'dbo' value ( eg. instead od dbo, 'JSmith' ).




Dazed and confused
(N+, MCP, MCAD)

 

[dyarwood]

dbo does mean Database owner. I have just done a quick query in QA replacing dbo with another user and it caused an error. However, you will probably find areas where it could be used. Which areas were you trying to use it in?

 

[dyarwood]

Just a quick thought (not tried it) but if you create a login for a given user and give that user access to a database using a different username to dbo and give that user name the role of db_owner then you might be able to use the new user as a database owner.

the user dbo is created when all db's are created and given the role of db_owner automatically.

 

[Skittle]

I've spotted it in a few examples but the one this morning that prompted me to check is in the setting of extended properties on a column:-

sp_addextendedproperty 'My extended name',
'My extended value',
'user', dbo,
'table', 'orders',
'column', 'orderid'



Dazed and confused
(N+, MCP, MCAD)

 

[mrdenny]

The dbo is the mapped name to the database owner.

When a user creates an object the users username is appended to the begining of the object name. (This applies to non-sysadmin users, I'll cover that in a minute.) For example, if I'm a user on your system, and my username is mrdenny, and I create a table named test1 that tables full name will be "mrdenny.test1". If I want to query this table I can do it in two ways.

select *
from test1
go
select *
from mrdenny.test1

For any other user to access my table they will need to use the second command shown above. This can cause problems with developers who do not qualify the owner of the object. Even if I have db_owner rights to the database, unless I am the database owner if I don't specify the dbo. in the create script the object will be created with my personal account as the owner. To test this you can create a non-sysadmin account, give it db_owner access to a database and run the following two scripts.

create table test1
(C1 varchar(10))
go
create table dbo.test1
(C1 varchar(10))
go

When you look in Enterprise Manager you will see two tables named test1. One is dbo.test1 and the other is {YourUserName}.test1. If the user is the database owner then you will not have this problem. As the database owner your username within the database is dbo, so all objects you create will by default be created with dbo as the owner.

If you create an object as a user with the sysadmin fixed server role, you will by default be a dbo so all objects will by default be created with dbo as the owner.

As I said, the only time this should be a problem is when users are creating objects with out specifying the dbo as the owner.

I hope this answered your question. If not, let me know and I'll try to clear up anything I've confused you on.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[Skittle]

Absolutely fantastic.

Thanks MrDenny.


Dazed and confused
(N+, MCP, MCAD)

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[dyarwood]

Very nice explanation Denny. (Looks like I've got a lot more to learn)

 

[mrdenny]

Not a problem. With SQL Server there is always more to learn.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)