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).
Note
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
Note
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.
User-Schema Separation
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.
Note
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.
Note
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.
External Resources
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. |
See Also
Concepts
Application Security Scenarios in SQL Server (ADO.NET)
Authentication in SQL Server (ADO.NET)
Server and Database Roles in SQL Server (ADO.NET)
Authorization and Permissions in SQL Server (ADO.NET)