What’s dbo?

2

January 22, 2018 by Kenneth Fisher

Don’t you hate it how certain words (well, acronyms in this case) get reused to mean multiple things? dbo seems to cause a great deal of confusion because of this.

First, dbo is an acronym standing for DataBase Owner. And there are three common usages for that I can think of off the top of my head. Feel free to point out others in the comments below and I’ll add them to the list 🙂

The actual owner of the database.

When talking about permissions this is the actual dbo. There is only one and they can’t be denied and they can do pretty much anything they want in the database.

Members of/have the permissions of the db_owner role.

“She’s dbo.” “He’s a member of dbo.” etc. This is a colloquial use of the term and isn’t technically accurate. The database owner (dbo) is not quite the same as the role db_owner. I generally prefer the more accurate terms (db_owner over dbo) but even I make mistakes here sometimes.

These first two are really not all that different. I’ve seen it create a little bit of confusion every now and again but not often and not much.

The schema.

This is where people start having difficulties. The schema dbo is the most commonly seen usage but it does not have anything to do with permissions. Schemas are containers that hold the objects within a database. They are third part of a fully defined four-part name (InstanceName.DatabaseName.SchemaName.ObjectName). The dbo schema is no different from the schema Sales. Well, other than the fact that most objects in almost every database are put in the dbo schema and I rarely see the Sales schema outside of AdventureWorks databases. Effectively though, they are both just schemas. The owner of the dbo schema has a lot of power, but only because so many objects are in that schema. If you create a database with a schema named Sales and put all of the objects under that schema rather than dbo then the owner of Sales has more power than the owner of dbo.

Obviously the difference between the permissions dbo and the schema dbo is pretty significant. In the end, you’ll have to use context to figure out which dbo you are working with, and if you aren’t sure then you need to look closer and/or ask questions.

2 thoughts on “What’s dbo?

  1. Chris Morse says:

    Interesting — thanks. Does the naming of the schema have any affect on application users? In other words, does a user have be a member of the “Sales” group in order to have access to the “Sales” schema via the application or report service?

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

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,163 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: