INF: Cross-Database Ownership Chaining Behavior Changes in SQL Server 2000 Service Pack 3

Microsoft SQL Server Service Pack 3 (SP3) provides a new security enhancement related option for configuring cross-database ownership chaining, Enable cross-database ownership chaining for all databases during setup. This article discusses the cross-database ownership chaining behavior in SQL Server 2000 SP3. With this new option, you can control whether or not you permit cross-database ownership chaining. By default, this option is disabled. Microsoft recommends that you use the default option, because it makes your database server more secure.

Ownership Chaining

By default, all database objects have owners. When an object such as a view, a stored procedure, or a user-defined function references another object, an ownership chain is established. For example, a table that is owned by the same user. When the same user owns the source object, the view, stored procedure, or user-defined function, and all target objects (underlying tables, views, or other objects), the ownership chain is said to be unbroken. When the ownership chain is unbroken, SQL Server checks permissions on the source object but not on the target objects.

Cross-Database Ownership Chaining

Cross-database ownership chaining occurs when the source object depends on objects in another database. A cross-database ownership chain works in the same way as ownership chaining in a database, except that an unbroken ownership chain is based on all the object owners being mapped to the same login account. Therefore, in a cross-database ownership chain, if the source object in the source database and the target objects in the target databases are owned by the same login account, SQL Server does not check permissions on the target objects.

If you have more than one database used by an application, and that application calls stored procedures or views in a database that is based on objects in another database, then cross-database ownership chaining is used. Applications that rely on cross-database ownership chaining may generate permission denied errors if cross-database ownership chaining option is turned off.

Risks Associated with Cross-Database Ownership Chaining

Microsoft recommends that you disable the cross-database ownership chaining option because of the actions that highly-privileged users can perform:

• Database owners and members of the db_ddladmin or the db_owners database roles can create objects that are owned by other users. These objects can potentially target objects in other databases. This means that if you enable cross-database ownership chaining, you must fully trust these users with data in all databases. To identify the members of the db_ddladmin and the db_owners roles in the current database, execute the following Transact-SQL commands:
  

  exec sp_helprolemember 'db_ddladmin' exec sp_helprolemember 'db_owner'

• Users with CREATE DATABASE permission can create new databases and attach existing databases. If cross-database ownership chaining is enabled, these users can access objects in other databases from newly created or attached databases.

Even though Microsoft recommends that you turn off cross-database ownership chaining for maximum security, there are some environments where you can fully trust your highly-privileged users; therefore, you can enable cross database ownership for specific databases to meet the requirements of specific applications.

How to Configure Cross-Database Ownership Chaining During Setup

In Microsoft SQL Server Service Pack 3 (SP3) Setup, a new dialog box has been added to allow the system administrator to control whether or not cross database ownership chaining will be permitted. If you select Enable cross-database ownership chaining for all databases during the SQL Server 2000 SP3 setup, you are enabling this option across all databases. This was the default behavior before SQL Server 2000 SP3. Regardless of the option that you select during setup, you can later modify server and database support for cross-database ownership chaining either by using Transact-SQL commands or from SQL Server Enterprise Manager.

How to Configure Cross-Database Ownership Chaining After Installation

To change the cross-database ownership chaining configuration, use the new options in the sp_configure and the sp_dboption stored procedures.

Note If you detach and then reattach a database, you must re-enable cross-database ownership chaining.

• Configuring cross-database ownership chaining by using Transact-SQL commands:
  • Configure cross-database ownership chaining support for the instance of SQL Server with the new Cross DB Ownership Chaining option for sp_configure. When this option is set to 0, you can control cross-database ownership chaining at the database level by using sp_dboption. When this option is set to 1, you cannot restrict cross-database ownership chaining. This is the pre-SQL Server 2000 SP3 behavior. If you change this option, include the RECONFIGURE option to reconfigure the instance without having to restart it. For example, use the following command to allow cross-database ownership chaining in all databases:
    

    EXEC sp_configure 'Cross DB Ownership Chaining', '1'; RECONFIGURE 
    

  • Configure cross-database ownership chaining at the database level with the new db chaining option for sp_dboption. When this option is set to false, the database cannot participate in cross-database ownership chaining as either the source or the target database. When this option is set to true, the database can participate in a cross-database ownership chain. By default, this option is false for all user databases after you apply SQL Server 2000 SP3. The following command enables cross-database ownership chaining for the Northwind database:
    

    EXEC sp_dboption 'Northwind', 'db chaining', 'true'

  
  The effects of sp_dboption are manifested only when the sp_configure Cross DB Ownership Chaining option is set to 0. Also, to enable cross-database ownership chaining at the database level, you must enable this option on both the source and the target database.
Configuring cross-database ownership chaining by using SQL Enterprise Manager:
• To set this option for all databases, follow these steps:
  1. Right-click <server>.
  2. Click to select Properties.
  3. Click Security.
  4. Click to select Allow cross-database ownership chaining in the Ownership chaining section.
  5. Click OK. You are prompted to stop and restart the SQL Server services.
  6. Click OK .
• To enable this option at the database level, follow these steps:
  1. Right-click the <database>.
  2. Click to select Properties.
  3. Click Options.
  4. Click to select Allow Cross Database Ownership Chaining in the Settings section