What is the difference between "db_owner" and "the user that owns the database" in SQL Server 2000?

I'm trying to better understand why one of our database update scripts failed to work properly at a particular customer site, and narrowed it down (I think) to database ownership and roles.

Disclaimer: I'm actually waiting to hear back from the customer's DBA so they can tell us if they upgraded their SQL database recently and so we can look at their database. I'm thinking a SQL 2000 to SQL 2005 conversion might have hosed our scripts if our applications's database login was converted to a schema, because we were referencing dbo in a few places in the update script.

Anyway, I've been trying to find a better explanation of database ownership and roles and how it impacts what owner a database object is actually assigned when you don't explicitly specify the owner in a T-SQL statement. For example, our update scripts typically just do CREATE TABLE foo instead of CREATE TABLE dbo.foo or something else, but I found a few that were explicitly using dbo, and those are the ones causing problems at the moment (only for this one customer).

I found this article (specific to SQL Server 2000), but the table on that page is confusing. It mentions db_owner and "owns the database" as two distinct possibilities for what role a user can have.

For example, the table states that if a user sam, who is in the db_owner role, runs the query CREATE TABLE [test3](abc int), it will be owned by sam.

It then mentions that if a another user sue, who "owns the database" (sic), runs the same query, it will be owned by dbo.

Wouldn't db_owner and "owns the database" be the same thing? The table implies that there is a difference between "being in the db_owner role" and actually "being the owner of the database." But, if that's, true, what does it mean to "own the database" if it's something other than being a member of the db_owner role?

No, db_owner and the owner of the database are not the same. dbo is a user and db_owner is a database role. Databases are owned by logins. Whatever login owns the database is aliased as dbo inside the database. You can change the database owner by using the sp_changedbowner system stored procedure.

All objects in a database are owned by a user. Users that are members of the db_owner role, among other permissions, are allowed to create objects owned by dbo. If a user is not a member of db_owner, but has some create permissions (e.g. Create Table), then any objects they create will be owned by the user that created them. You can change the ownership of an object using sp_changeobjectowner system stored procedure.

Clear and to the point. Awesome answer! For anyone else reading this, I would just like to add one more thing I found through more research and some testing. If the database login is a member of the `sysadmin` server role, database objects created by that login will be owned by `dbo`, and (for SQL Server 2005), they will also added to the `dbo` schema by default, unless you specify a different owner/schema.

Mike Spross 2010-04-29 01:27:55

Incidentally, I think I know what the problem was now. Normally we give customers an empty database that they attach to their SQL Server when they install the application. All the objects are owned by `dbo`. We also have a script that creates the database login used by our application. It turns out this script was adding the application's login to the `sysadmin` server role (no idea why it was written that way). My guess is a diligent DBA saw this and removed our login from the `sysadmin` role, since they have more than one database on their server and our app clearly shouldn't be a `sysadmin`

Mike Spross 2010-04-29 01:32:09

Our script also didn't map the login to a database user. The app works fine despite this since the login is created as a `sysadmin`. However, if a DBA removed the login from the `sysadmin` role, they would have had to map the login to a specific database user in our database so that our app could connect. When they did this, they set the user's default schema to be the same as the user name. Our update script dropped and recreated some database views, but we weren't prefixing the view names with an owner/schema, so they were being added to database user's schema instead of the `dbo` schema.

Mike Spross 2010-04-29 01:35:31

So, then, finally, there was a single `CREATE VIEW` query in our update script that **did** prefix the other views it was using with with a `dbo` prefix. Since the other views had already been dropped and re-created by the script, they were now living in the database user's schema, so the database couldn't find it in it the `dbo` schema.

Mike Spross 2010-04-29 01:39:51

@Mike Spross- Wow, what sorted state of affairs. Yeah, no app should have its user depend on being in sysadmins. db_owner maybe but not sysadmin. When the DBA made this change, did they test it first or just do it in the live environment?

Thomas 2010-04-29 03:22:35

@Thomas: I'm not sure. If they just removed the login from `sysadmin` they would have gotten errors since the login wasn't mapped to a database user. My guess is that must have ran into issues, then mapped the login to a database user, and let SQL Server create a default schema for that user.

Mike Spross 2010-04-29 15:09:07

ansaurus

tags:

views:

answers:

What is the difference between "db_owner" and "the user that owns the database" in SQL Server 2000?

related questions