Ownership and User-Schema Separation in SQL Server (ADO.NET)

A core concept of SQL Server security is that owners of objects have irrevocable permissions to administer them. You cannot remove privileges from an object owner, and you cannot drop users from a database if they own objects in it. In SQL 2000, if a user creates an object without specifying the owner, the user becomes the owner of the object. An object owner has irrevocable permissions to administer the object, that caused problems if multiple users own objects. Having all database objects owned by a single owner simplifies managing permissions in SQL Server 2000 by taking advantage of ownership chaining, as discussed in Authorization and Permissions in SQL Server (ADO.NET).

NoteNote

Members of the sysadmin fixed server role and the db_owner fixed database role also have irrevocable ownership permissions on all objects in a database.

Versions of SQL Server prior to SQL Server 2005 use the following four-part naming syntax for referring to objects.

Server.Database.ObjectOwner.DatabaseObject

If database users Bob and Sue each create Table1, you need to use the fully-qualified name. If a system administrator or database owner creates an object, it is owned by the dbo user account, not by an individual user.

SELECT col1 FROM Bob.Table1
SELECT col1 FROM Sue.Table1
SELECT col1 FROM dbo.Table1
NoteNote

It is not necessary to use the ObjectOwner.DatabaseObject syntax when referring to an object owned by dbo, although it is more efficient to do so. If the owner name is not supplied, SQL Server 2000 checks first to see if the object exists under the name of the current user, then it checks to see if the object is owned by dbo. Supplying the two-part name saves an extra step.

SQL Server 2005 introduced user-schema separation, which allows for more flexibility in managing database object permissions. A schema is a named container for database objects, which allows you to group objects into separate namespaces. For example, the AdventureWorks sample database contains schemas for Production, Sales, and HumanResources.

The four-part naming syntax for referring to objects specifies the schema name.

Server.Database.DatabaseSchema.DatabaseObject

Schema Owners and Permissions

Schemas can be owned by any database principal, and a single principal can own multiple schemas. You can apply security rules to a schema, which are inherited by all objects in the schema. Once you set up access permissions for a schema, those permissions are automatically applied as new objects are added to the schema. Users can be assigned a default schema, and multiple database users can share the same schema.

By default, when developers create objects in a schema, the objects are owned by the security principal that owns the schema, not the developer. Object ownership can be transferred with ALTER AUTHORIZATION Transact-SQL statement. A schema can also contain objects that are owned by different users and have more granular permissions than those assigned to the schema, although this is not recommended because it adds complexity to managing permissions. Objects can be moved between schemas, and schema ownership can be transferred between principals. Database users can be dropped without affecting schemas.

Built-In Schemas

SQL Server ships with ten pre-defined schemas that have the same names as the built-in database users and roles. These exist mainly for backward compatibility. You can drop the schemas that have the same names as the fixed database roles if you do not need them. You cannot drop the following schemas:

  • dbo

  • guest

  • sys

  • INFORMATION_SCHEMA

If you drop them from the model database, they will not appear in new databases.

NoteNote

The sys and INFORMATION_SCHEMA schemas are reserved for system objects. You cannot create objects in these schemas and you cannot drop them.

The dbo Schema

The dbo schema is the default schema for a newly created database. The dbo schema is owned by the dbo user account. By default, users created with the CREATE USER Transact-SQL command have dbo as their default schema.

Users who are assigned the dbo schema do not inherit the permissions of the dbo user account. No permissions are inherited from a schema by users; schema permissions are inherited by the database objects contained in the schema.

NoteNote

When database objects are referenced by using a one-part name in SQL Server 2005, SQL Server first looks in the user's default schema. If the object is not found there, SQL Server looks next in the dbo schema. If the object is not in the dbo schema, an error is returned.

For more information on object ownership and schemas, see the following resources.

Resource

Description

User-Schema Separation in SQL Server 2008 Books Online

Describes the changes introduced by user-schema separation. Includes new behavior, its impact on ownership, catalog views, and permissions.

User-Schema Separation in SQL Server 2005 Books Online

Describes the changes introduced by user-schema separation. Includes new behavior, its impact on ownership, catalog views, and permissions.

Using Ownership Chains in SQL Server 2000 Books Online

Describes how users can inherit permissions from object owners.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2015 Microsoft