Article ID: 810474 - View products that this article applies to.
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 ChainingBy 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 ChainingCross-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:
How to Configure Cross-Database Ownership Chaining During SetupIn 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 InstallationTo 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.
Contact us for more help
Connect with Answer Desk for expert help.