INF: Cross-Database Ownership Chaining Behavior Changes in SQL Server 2000 Service Pack 3 (810474)
The information in this article applies to:
- Microsoft SQL Server 2000 (all editions) SP3
SUMMARYMicrosoft 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. MORE INFORMATIONOwnership 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 ChainingMicrosoft recommends that you disable the cross-database ownership
chaining option because of the actions that highly-privileged users can
perform: 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:
- Right-click
<server>.
- Click to select
Properties.
- Click Security.
- Click to select Allow cross-database
ownership chaining in the Ownership chaining
section.
- Click OK. You are prompted to stop
and restart the SQL Server services.
- Click OK .
- To enable this option at the database level, follow
these steps:
- Right-click the
<database>.
- Click to select
Properties.
- Click Options.
- Click to select Allow Cross Database
Ownership Chaining in the Settings section
Modification Type: | Minor | Last Reviewed: | 3/18/2004 |
---|
Keywords: | kbinfo kbSysAdmin kbUpgrade kbSQLServ2000sp3fea KB810474 kbAudDeveloper kbAudITPRO kbAudOEM kbAudEndUser |
---|
|