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.
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
- 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
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
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:Configuring cross-database ownership chaining by using SQL Enterprise Manager:
- 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.
- To set this option for all databases, follow these
- Click to select
- Click Security.
- Click to select Allow cross-database
ownership chaining in the Ownership chaining
- Click OK. You are prompted to stop
and restart the SQL Server services.
- Click OK .
- To enable this option at the database level, follow
- Right-click the
- Click to select
- Click Options.
- Click to select Allow Cross Database
Ownership Chaining in the Settings section
Article ID: 810474 - Last Review: October 29, 2007 - Revision: 4.3
- Microsoft SQL Server 2000 Service Pack 3
|kbproductlink kbinfo kbsysadmin kbupgrade kbsqlserv2000sp3fea KB810474|