SCHEMA

SCHEMA
=========

-----------
USER "A"
-----------

--owns--> SCHEMA "S1"

--contains--> SP_1

<--owns-- DB_ROLE "R"

--owns--> SCHEMA "S2"

--contains--> TABLE_1

<--owns-- USER "B"

--owns--> SCHEMA "S3" --contains--> VIEW_1 <--owns-- USER "B", DB_ROLE "R"


-----------
USER "DBO"
-----------

--owns--> SCHEMA "DBO"


----------------
USER "DB_OWNER"
----------------

--owns--> SCHEMA "DB_OWNER"


----------------
DB_ROLE "R"
----------------

--owns--> SCHEMA "S2"

--contains--> TABLE_2

--owns--> SCHEMA "S3"

--contains--> VIEW_2


Schema can only contain certain entities, namely OBJECT (tbls, views, SPs, functions, queues and synonyms are objects that are "ownership-transferable" while other objects like linkedsrvrs, constraints, rules, triggers, credentials, statistics etc. are not), TYPE and XML SCHEMA COLLECTION.


**The only reliable way to find the owner of a object is to query the sys.objects catalog view. The only reliable way to find the owner of a type is to use the TYPEPROPERTY function.



A schema contains database (catalog) objects such as tables, views and SPs. A schema owner may be a DB user, DB role or app role.


DB user has one to many relationship with dbo schemas i.e. a schema can belong to only one user while a user can own many schemas.


"dbo" exists as a default schema. "dbo" also exists as a DB user. By default dbo user owns dbo schema.

"db_owner" is also both schema and user. So are other db_*,INFORMATION_SCHEMA etc. In fact, it is tempting to create the same schema name for every DB user. Is that a good practice?

Beginning with SQL Server 2005, a user can own an OBJECT or TYPE that is contained by a schema owned by another database user.