Use the cross db ownership chaining option to configure cross-database ownership chaining for an instance of Microsoft SQL Server.
This server option allows you to control cross-database ownership chaining at the database level or to allow cross-database ownership chaining for all databases:
When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated.
Mary owns most of the underlying objects of the July 2003 view. Because Mary has the right to make objects that she owns accessible to any other user, SQL Server behaves as though whenever Mary grants access to the first view in a chain, she has made a conscious decision to share the views and table it references. In real life, this might not be a valid assumption. Production databases are far more complex than the one in the illustration, and the permissions that regulate access to them rarely map perfectly to the administrative structures of the organizations that use them.
SQL Server 2005 implemented the concept of a database object schema. A schema is a distinct namespace to facilitate the separation, management, and ownership of database objects. It removed the tight coupling of database objects and owners to improve the security administration of database objects. Database object schemas offer functionality to control and help secure application objects within a database environment not available in previous versions of SQL Server.
In releases prior to SQL Server 2005, database object owners and users were the same things. SQL Server 2005 introduced the concept of database schemas and the separation between database objects and ownership by users. An object owned by a database user is no longer tied to that user. The object now belongs to a schema – a container that can hold many database objects. The schema owner may own one or many schemas.
A schema is separate entity within the database. It is created by using the CREATE SCHEMA statement. A schema can be owned by a user, a role, or a group (for more information about possible schema owners, see the “Principals” section in this document). A user executing CREATE SCHEMA can be the owner of the schema or it can allocate another user as the schema owner (with appropriate IMPERSONATE permissions). A schema only has one owner, but a user can own many schemas. Schema ownership is transferrable.
The default schema for a user can be defined by using the DEFAULT_SCHEMA option of CREATE USER or ALTER USER. If no default schema is defined for a user account, SQL Server will assume dbo is the default schema. It is important note that if the user is authenticated by SQL Server as a member of a group in the Windows operating system, no default schema will be associated with the user. If the user creates an object, a new schema will be created and named the same as the user, and the object will be associated with that user schema
When a schema is created, it is owned by a principal. A principal is any entity or object that has access to SQL Server resources. These are:
•Windows domain logins
•Windows local logins
•SQL Server logins
How Permissions Are Checked
A secured object has a four-part name (fully qualified name): server.database.schema.object. The object will also have permissions applied to it. Thus, the following checking is done on the object:
•Access is granted or denied directly on the object.
•Access is granted or denied on the schema containing the object.
•Access is granted or denied on the database containing the schema.
•Access is granted or denied on the server containing the database.
During the checks, DENY permissions are checked first and access is denied if it exists at any of the check levels above. Similarly, if no specific permission exists, access is denied.
Access is always granted to SA and to members of the sysadmin group.
When multiple objects are accessed sequentially, such as in a stored procedure or a view, that is known as chaining. Chaining allows the improvement in performance of checking object permissions. As a first step in the checking process, the object owner (schema) of the called object is compared to the owner of the calling object. If the owners are the same, the permissions of the called object are not checked. A chain of objects with the same owner is called an unbroken ownership chain.
In the scenario described above, views and stored procedures will normally access objects that are owned by different schemas. In this situation, the owner of the called object is compared with the owner of the calling object. If the owners are different, the full set of permissions is checked on the called object. This is referred to as a broken ownership chain.
The assessment process can be improved by using either the fully qualified name or the DEFAULT_SCHEMA option described earlier. By setting a value for DEFAULT_SCHEMA for the user, the server will check the DEFAULT_SCHEMA first, removing an unnecessary ownership checking process. This can improve performance considerably on heavily utilized systems.
For more information about the DEFAULT_SCHEMA and the performance considerations when not using fully qualified object names, see Upgrading to SQL Server 2005 and default schema setting.
When designing an application for SQL Server, you rarely want users to have full permissions to access the tables in the database. Many applications are designed to perform all database access through stored procedures, and it is through the stored procedures users can access and update data. The procedures perform validations of business rules to protect the integrity of the database.