SQL Server 2000 中的“db_owner”和“拥有数据库的用户”有什么区别？

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

提问by Mike Spross

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 dboin a few places in the update script.

免责声明：我实际上正在等待客户 DBA 的回音，以便他们可以告诉我们他们最近是否升级了 SQL 数据库，以便我们查看他们的数据库。我认为如果我们的应用程序的数据库登录转换为模式，SQL 2000 到 SQL 2005 的转换可能会影响我们的脚本，因为我们dbo在更新脚本中的几个地方引用了。

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 fooinstead of CREATE TABLE dbo.fooor 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).

无论如何，我一直试图找到更好的解释，说明数据库所有权和角色，以及当您没有在 T-SQL 语句中明确指定所有者时，它如何影响实际分配的数据库对象的所有者。例如，我们的更新脚本通常只是执行CREATE TABLE foo而不是CREATE TABLE dbo.foo其他操作，但我发现一些明确使用dbo，而这些是目前导致问题的那些（仅针对该客户）。

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

我找到了这篇文章（特定于 SQL Server 2000），但该页面上的表格令人困惑。它提到db_owner和“拥有数据库”作为用户可以拥有的角色的两种不同的可能性。

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

例如，该表规定，如果一个用户sam，谁是谁的db_owner作用，运行查询CREATE TABLE [test3](abc int)，将所拥有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.

然后它提到，如果sue“拥有数据库”（原文如此）的另一个用户运行相同的查询，它将归dbo.

Wouldn't db_ownerand "owns the database" be the same thing? The table implies that there is a difference between "being in the db_ownerrole" 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_ownerrole?

不会db_owner和“拥有数据库”是一回事吗？该表暗示“处于db_owner角色中”和实际“成为数据库的所有者”之间存在差异。但是，如果这是真的，那么“拥有数据库”是什么意思，如果它不是db_owner角色的成员？

回答by Thomas

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 dboinside the database. You can change the database owner by using the sp_changedbownersystem stored procedure.

不，db_owner 和数据库的所有者不一样。dbo 是用户，db_owner 是数据库角色。数据库归登录名所有。无论登录名拥有数据库，别名都为dbo数据库内部。您可以使用sp_changedbowner系统存储过程更改数据库所有者。

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_changeobjectownersystem stored procedure.

数据库中的所有对象都归用户所有。作为 db_owner 角色成员的用户以及其他权限，可以创建由 dbo 拥有的对象。如果用户不是 db_owner 的成员，但具有某些创建权限（例如创建表），则他们创建的任何对象都将归创建它们的用户所有。您可以使用sp_changeobjectowner系统存储过程更改对象的所有权。