Ownership in SQL 2005

Hi there,
maybe I've misunderstood something but i just read this phrase in BOL :
"All tables in the partition's schema must have the same owner; for example,
you cannot have a FROM clause that references the tables [tk].[customer],
[john].[store], and [dave].[sales_fact_2004]"
Talking about SQL 2005 I would think that we now are refering to schemas and
in this example it is 3 schemas and they can easily have the same owner.. Or
am I wrong ? BOL furthermore states that using schemas make things a lot
easier. For the most I guess. But you can now have one user owning the
schema and another user owning a table in that schema, so how does this make
the transfering of an ownership easier ? I've read the following : "Now
database objects are owned by schemas. Users no longer direcly own database
objects; instead they own schemas". Well that would have simplified things
but apparently it isn't so! The MOC 2733 states the following about schemas
: "Improved manageability, because dropping a user does not necessitate the
renaming of all objects that the user owns". In the above example I could
have a schema owned by "dbo" and maby 20 tables in that schema owned by
"user1". How do I easily drop that user ? And here's some more fun :
Creating a schema owned by dbo and creating a new table in that schema the
table inherits the ownership from the schema so therefore the owner of the
table is dbo, too. Pretty straightforward. If I change the ownership of the
schema to user1 the table inherits the ownership and changing it back to dbo
does the same thing. But changing the ownership explicitly to the table
changes things. Changing the ownership of the schema does NOT change the
ownership of the table :))
Anyone have some really good article explaining this ?
Regards :)
Bobby Henningsen> "All tables in the partition's schema must have the same owner; for
> example,
> you cannot have a FROM clause that references the tables [tk].[customer],
> [john].[store], and [dave].[sales_fact_2004]"
> Talking about SQL 2005 I would think that we now are refering to schemas
> and
> in this example it is 3 schemas and they can easily have the same owner..
> Or
> am I wrong ?
The context of this BOL statement is requirements for indexed views. The
'owner' requirement for indexed views is the schema owner, not the schema
name. It is possible to create an index on a view referencing tables in
different schema as long as schema share the same owner.
> I've read the following : "Now database objects are owned by schemas.
I don't know where this excerpt is from. Database objects are owned by
users as they always have been. The difference in SQL 2005 is that
ownership is via the schema owner so the schema name can be different than
the owner name. Consider:
CREATE USER Bobby WITHOUT LOGIN
GO
CREATE SCHEMA john AUTHORIZATION Bobby
CREATE SCHEMA dave AUTHORIZATION Bobby
GO
CREATE TABLE [john].[store](...)
CREATE TABLE [dave].[sales_fact_2004](...)
GO
In this example, the user Bobby owns both tables because schema 'john' and
'dave' are both owned by user 'Bobby'.
> How do I easily drop that user ?
To drop user Bobby, you would first need to change schema owner to another
user:
ALTER AUTHORIZATION ON SCHEMA::john TO SomeOtherUser;
ALTER AUTHORIZATION ON SCHEMA::dave TO SomeOtherUser;
GO
DROP USER Bobby
GO
It gets confusing because a schema name and owner name can also be the same.
Be mindful that these are actually separate.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Bobby Henningsen" <bobhen@.mail.dk> wrote in message
news:OkjqgExXGHA.1200@.TK2MSFTNGP03.phx.gbl...
> Hi there,
> maybe I've misunderstood something but i just read this phrase in BOL :
> "All tables in the partition's schema must have the same owner; for
> example,
> you cannot have a FROM clause that references the tables [tk].[customer],
> [john].[store], and [dave].[sales_fact_2004]"
> Talking about SQL 2005 I would think that we now are refering to schemas
> and
> in this example it is 3 schemas and they can easily have the same owner..
> Or
> am I wrong ? BOL furthermore states that using schemas make things a lot
> easier. For the most I guess. But you can now have one user owning the
> schema and another user owning a table in that schema, so how does this
> make
> the transfering of an ownership easier ? I've read the following : "Now
> database objects are owned by schemas. Users no longer direcly own
> database
> objects; instead they own schemas". Well that would have simplified things
> but apparently it isn't so! The MOC 2733 states the following about
> schemas
> : "Improved manageability, because dropping a user does not necessitate
> the
> renaming of all objects that the user owns". In the above example I could
> have a schema owned by "dbo" and maby 20 tables in that schema owned by
> "user1". How do I easily drop that user ? And here's some more fun :
> Creating a schema owned by dbo and creating a new table in that schema the
> table inherits the ownership from the schema so therefore the owner of the
> table is dbo, too. Pretty straightforward. If I change the ownership of
> the
> schema to user1 the table inherits the ownership and changing it back to
> dbo
> does the same thing. But changing the ownership explicitly to the table
> changes things. Changing the ownership of the schema does NOT change the
> ownership of the table :))
> Anyone have some really good article explaining this ?
> Regards :)
> Bobby Henningsen
>
>