Re: How many objects are owned by schema

I'm not Dan, but my guess to what he meant is:

You can create a user (a.k.a. "database principal") without specifying a login, as in:

CREATE USER no_login WITHOUT LOGIN

Yes, Tibor, a user without a login was exactly what I was referring to. Schema and objects must be owned by a valid database principal but the database principal doesn't necessarily need to be associated with a login for ownership purposes.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Tibor Karaszi" <tibor_please.no.email_karaszi@xxxxxxxxxxxxxxxxxx> wrote in message news:OyYm1lWzJHA.1420@xxxxxxxxxxxxxxxxxxxxxxx
I'm not Dan, but my guess to what he meant is:

You can create a user (a.k.a. "database principal") without specifying a login, as in:

CREATE USER no_login WITHOUT LOGIN


I have a feeling that Dan was referring to such a user...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Martin" <martin_remove_this_@xxxxxxxxxxxxx> wrote in message news:30FDEA56-BE2E-4DA7-B828-D013B7A06CEF@xxxxxxxxxxxxxxxx
Hi dan,

thanks for the reply and pointing me to your article, however I have a question about that.

you state in the post

"Note that an owner can be any database principal and does not necessarily need to be associated with a login." - start of 2nd last paragraph.

how does this work. I tried it myself and got an error.

ALTER AUTHORIZATION ON OBJECT::dbo.testtable TO myself; ("myself" is neither a server login or a user in the database)

my understanding is that a login is a server level object (gives a user access to the server) and a user is a specific login that has been granted access to a particular database.

I do not understand how
1. an object can be owned by anthing that does not have access to either the db or he server.

I guess you could create a role, and that would get added to sys.database_principles and then that role could own an object.

can you shed any light on this please - perhaps I have interpretted you incorrectly.


also

I created an table in a test database

create table dbo.testtable
(
test int
)


next I altered the authorization to a user - Note "testuser" is a database user and not a schema.

ALTER AUTHORIZATION ON OBJECT::dbo.testtable TO testuser;

That is fine - works a treat.
I refreshed the database tree in object explorer SSMS.

in the tables branch "testtable " is still seen as " dbo.testtable" and not " testuser.testtable"

I looked at sys.objects and the data "principle_id" column hat was NULL, now has the ID of "testuser" - although the "schema_id" column still has the id of "dbo"

I attempted to alter the authorization back to dbo

ALTER AUTHORIZATION ON OBJECT::testuser.testtable TO dbo; --- did not work -- testuser.testtable does not exist

however

ALTER AUTHORIZATION ON OBJECT::dbo.testtable TO dbo; -- works fine.

I am assuming a principle owning an object is different that a schema owing an object.
I guess object are only meant to be owned by schemas.

an thoughts on this.

Thanks for your time.

cheers

martin.







"Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:#aMzCXRzJHA.2324@xxxxxxxxxxxxxxxxxxxxxxx
I actually thought that all objects belonged to schemas in 2k5/8.

This is true. Schema-contained objects belong to exactly one schema. Separately, every schema has an owner (a.k.a. authorization) and object ownership is inherited from the containing schema unless specified otherwise.

in what circumstances would an object belong to a user - how would you do this.

Although seldom done in practice, one can specify an owner at the object level to override inheritance from the schema. This is done via ALTER AUTHORIZATION:

ALTER AUTHORIZATION ON OBJECT::dbo.Foo TO SomeOtherDatabasePrincipal;

I blogged my thoughts on schema and ownership at http://weblogs.sqlteam.com/dang/archive/2008/02/03/Keep-Schema-and-Ownership-Simple.aspx.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Martin" <martin_remove_this_@xxxxxxxxxxxxx> wrote in message news:72DB4628-03C2-419D-A2A6-F62D68493FD4@xxxxxxxxxxxxxxxx
Hi Tibor,

That is exactly what I meant.
You interpreted my question correctly.
I'll try to use the correct terminology next time.

however, one of the point you seem to make is

An object is also owned by a user (most often the same user as the one who owns the schema the object is in).

I actually thought that all objects belonged to schemas in 2k5/8.
in what circumstances would an object belong to a user - how would you do this.

cheers

martin.


"Tibor Karaszi" <tibor_please.no.email_karaszi@xxxxxxxxxxxxxxxxxx> wrote in message news:O79#h6JzJHA.480@xxxxxxxxxxxxxxxxxxxxxxx
Martin,

Something doesn't add up here. An object is in a schema. A schema is owned by a user. An object is also owned by a user (most often the same user as the one who owns the schema the object is in). But an object is not owned by a schema. Perhaps you meant to identify what objects *are* in a schema? That would be a simple join between sys.objects and sys.schemas (using the schema_id column).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Martin" <martin_remove_this_@xxxxxxxxxxxxx> wrote in message news:3B8EA770-667D-45E9-ABD9-07F64063DBFE@xxxxxxxxxxxxxxxx
hi,

I am looking to identify objects that are owned by a schema.
Is there a way of querying this from the system tables at all.
I'd prefer to do this via a query rather than looking through SSMS.

I managed to write a query to get the owner

select * from sys.schemas
join sys.sysusers
ON sys.schemas.name = sys.sysusers.name
where sys.schemas.principal_id between 5 and 16383;

I guess that I need to join this onto another table - maybe sysobjects - but I am not sure.

any help appreciated

cheers

martin.







.

Relevant Pages

  • Re: How many objects are owned by schema
    ... "Dan Guzman" wrote in message ... CREATE USER no_login WITHOUT LOGIN ... Schema and objects must be owned by a valid database principal but the database principal doesn't necessarily need to be associated with a login for ownership purposes. ... Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: How many objects are owned by schema
    ... CREATE USER no_login WITHOUT LOGIN ... "Note that an owner can be any database principal and does not necessarily need to be associated with a login." ... ALTER AUTHORIZATION ON OBJECT::dbo.testtable TO myself; ... I am assuming a principle owning an object is different that a schema owing an object. ...
    (microsoft.public.sqlserver.programming)
  • user/schema problem in SQL Server 2005!
    ... 1:Use Manage Studio login the server with Integrated security. ... 2:Create a dabase named testdb; ... 4:Create a SQL Server login named guxiaobo,set it's default databse to ... 7:In database testdb create a schema schema1 owned by dbo; ...
    (comp.databases.ms-sqlserver)
  • Re: Default schema not working properly for AD groups
    ... CREATE SCHEMA test AUTHORIZATION ... CREATE LOGIN FROM WINDOWS; ... SQL Server MVP ... We are running SQL 2005 SP2. ...
    (microsoft.public.sqlserver.security)
  • Re: restoring dB and login to new server
    ... Tibor Karaszi, SQL Server MVP ... that looks like it would simplify things a bit...but I still need to create the login manually? ... databases between servers by doing a full backup, then restoring on the new ... database, I get an error that the "user or role already exists". ...
    (microsoft.public.sqlserver.tools)