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.
Category: Microsoft SQL Server, Security, SQLServerPedia Syndication, T-SQL | Tags: microsoft sql server, object schema, security, T-SQL
6 thoughts on “What’s dbo?”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
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?
It depends on the default schema. Most of the time the default schema is dbo, so yes, you would have to specify “Sales” as your schema. If on the other hand you set your default to Sales then Table1 would mean Sales.Table1 not dbo.Table1. I wrote about it a bit more here: https://sqlstudies.com/2016/04/06/name-the-schema-when-you-create-an-object-or-else/
Thanks for this interesting post, I have shared itt on Twitter.
[…] you own one of them? Or why some other user owns the database? Just to be clear, by owner I mean the one and only actual owner of the database, not just a member of the db_owner role. This can be a bit confusing when you know that you have a […]
[…] If you want to hit everyone you can always do the DENY on the public role. Well, everyone but dbo and members of […]
[…] + 여기서 자세히 보기 […]