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.
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.