User-Schema Separation
The behavior of schemas changed in SQL Server 2005. Schemas are no longer equivalent to database users; each schema is now a distinct namespace that exists independently of the database user who created it. In other words, a schema is simply a container of objects. A schema can be owned by any user, and its ownership is transferable.
New Behavior
The separation of ownership from schemas has important implications:
Ownership of schemas and schema-scoped securables is transferable. For more information, see ALTER AUTHORIZATION (Transact-SQL).
Objects can be moved between schemas. For more information, see ALTER SCHEMA (Transact-SQL).
A single schema can contain objects owned by multiple database users.
Multiple database users can share a single default schema.
Permissions on schemas and schema-contained securables can be managed with greater precision than in earlier releases. For more information, see GRANT Schema Permissions (Transact-SQL) and GRANT Object Permissions (Transact-SQL).
A schema can be owned by any database principal. This includes roles and application roles.
A database user can be dropped without dropping objects in a corresponding schema.
Code written for earlier releases of SQL Server may return incorrect results, if the code assumes that schemas are equivalent to database users.
Catalog views designed for earlier releases of SQL Server may return incorrect results. This includes sysobjects.
New Catalog Views
Beginning with SQL Server 2005, schemas are explicit entities reflected in metadata; and as a result, schemas can only have one owner but a single user can own many schemas. This complex relationship is not reflected in the SQL Server 2000 system tables, so SQL Server 2005 introduced new catalog views, which accurately reflect the new metadata.
The following table below shows the mapping between the SQL Server 2000 system tables and the equivalent SQL Server 2005 and later catalog views.
SQL Server 2000 system table |
SQL Server 2005 and later catalog view |
|---|---|
sysusers |
sys.database_principals sys.schemas |
syslogins |
sys.server_principals |
SQL Server 2005 introduced more than 250 new catalog views. We strongly recommend that you use the new catalog views to access metadata. For more information, see Catalog Views (Transact-SQL).
New DDL statements can introduce complexities to system metadata that are not accurately reflected in old catalog views such as sysobjects. In this example, the user ID and schema name returned by sysobjects are out of sync, and do not reflect the distinction between user and schema introduced in SQL Server 2005.
use tempdb
go
create login u1 with password = 'Mdfjd$sakj943857l7sdfh##30'
create user u1 with default_schema = u1
go
grant create table to u1
go
create schema sch1
go
create schema u1 authorization u1
go
execute as user = 'u1'
go
create table t1(c1 int)
go
revert
go
select user_name(uid) , * from sysobjects where name = 't1'
go
Warning
You must use the new catalog views in any database in which any of the following DDL statements has ever been used: CREATE/ALTER/DROP SCHEMA; CREATE/ALTER/DROP USER; CREATE/ALTER/DROP ROLE; CREATE/ALTER/DROP APPROLE; ALTER AUTHORIZATION.
Default Schemas
In order to resolve the names of securables that are not fully qualified names, SQL Server 2000 uses name resolution to check the schema owned by the calling database user and the schema owned by dbo.
Beginning with SQL Server 2005, each user has a default schema. The default schema can be set and changed by using the DEFAULT_SCHEMA option of CREATE USER or ALTER USER. If DEFAULT_SCHEMA is left undefined, the database user will have dbo as its default schema.
See Also